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

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


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


enter image description here

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



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!

  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.

enter image description hereenter image description here


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

=COUNTIF(B:B,A1)

Regards!