Conditionally formatting cells if their value equals any value of another column

Question

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

User · Answer

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

User · Answer

Another simpler solution is to use this formula in the conditional formatting  apply to column A     COUNTIF B B A1    Regards

User · Answer

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    Select the columns to compare click Conditional Formatting click Highlight Cells Rules click Duplicate Values  the defaults should be OK  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

User · Answer

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  gt 0

User · Answer

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   amp  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

User · Answer

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

[excel] Conditionally formatting cells if their value equals any value of another column

Examples related to excel

Examples related to vba

Examples related to excel-formula

Examples related to conditional-formatting