Conditional formatting cells if their value equals

Question

I have data in the A and B column. 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 if the values in B exists there. If a row matches, I need to change the row's background colour in A to red or something.

What function do I need to use, and how?

Sorry, I'm a newbie in Excel.

Answer 1

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

Answer 2

Another simpler solution is to use this formula in the conditional formatting (apply to column A):

=COUNTIF(B:B,A1)

Regards!

Answer 3

No formulas required. This works on as many columns as you need, but will only compare columns in the same worksheet:

1. Select the columns to compare
2. click Conditional Formatting
3. click Highlight Cells Rules
4. click Duplicate Values (the defaults should be OK)

5. Duplicates are now highlighted in red

   • Bonus tip, you can filter each row by colour to either leave the unique values in the column, or leave just the duplicates.

Answer 4

Here is the formula

create a new rule in conditional formating based on a formula. Use the following formula and apply it to $A:$A

=NOT(ISERROR(MATCH(A1,$B$1:$B$1000,0)))

here is the example sheet to download if you encounter problems

---

UPDATE
here is @pnuts's suggestion which works perfect as well:

=MATCH(A1,B:B,0)>0