I have data in the A
and B
columns. B
column's data is mostly duplicates of A
's data, but not always. For example:
A
Budapest
Prague
Paris
Bukarest
Moscow
Rome
New York
B
Budapest
Prague
Los Angeles
Bukarest
I need to search the A
column for the values in B
. If a row matches, I need to change the row's background colour in A
to red or something.
This question is related to
excel
vba
excel-formula
conditional-formatting
I was looking into this and loved the approach from peege using a for loop! (because I'm learning VBA at the moment)
However, if we are trying to match "any" value of another column, how about using nested loops like the following?
Sub MatchAndColor()
Dim lastRow As Long
Dim sheetName As String
sheetName = "Sheet1" 'Insert your sheet name here
lastRow = Sheets(sheetName).Range("A" & Rows.Count).End(xlUp).Row
For lRowA = 1 To lastRow 'Loop through all rows
For lRowB = 1 To lastRow
If Sheets(sheetName).Cells(lRowA, "A") = Sheets(sheetName).Cells(lRowB, "B") Then
Sheets(sheetName).Cells(lRowA, "A").Interior.ColorIndex = 3 'Set Color to RED
End If
Next lRowB
Next lRowA
End Sub
Another simpler solution is to use this formula in the conditional formatting (apply to column A):
=COUNTIF(B:B,A1)
Regards!
I unable to comment on the top answer, but Excel actually lets you do this without adding the ugly conditional logic.
Conditional formatting is automatically applied to any input that isn't an error, so you can achieve the same effect as:
=NOT(ISERROR(MATCH(A1,$B$1:$B$1000,0)))
With this:
= MATCH(A1,$B$1:$B$1000,0)))
If the above is applied to your data, A1 will be formatted if it matches any cell in $B$1:$B$1000, as any non-match will return an error.
All you need to do for that is a simple loop.
This doesn't handle testing for lower case, upper-case mismatch.
If this isn't exactly what you are looking for, comment, and I can revise.
If you are planning to learn VBA. This is a great start.
TESTED:
Sub MatchAndColor()
Dim lastRow As Long
Dim sheetName As String
sheetName = "Sheet1" 'Insert your sheet name here
lastRow = Sheets(sheetName).Range("A" & Rows.Count).End(xlUp).Row
For lRow = 2 To lastRow 'Loop through all rows
If Sheets(sheetName).Cells(lRow, "A") = Sheets(sheetName).Cells(lRow, "B") Then
Sheets(sheetName).Cells(lRow, "A").Interior.ColorIndex = 3 'Set Color to RED
End If
Next lRow
End Sub
No formulas required. This works on as many columns as you need, but will only compare columns in the same worksheet:
NOTE: remove any duplicates from the individual columns first!
Duplicates are now highlighted in red
Source: Stackoverflow.com