compare a cell with a cell range?

[tuzlak]

Hello,
I'm not any excel authorithy, so please be gentle.

I have a range of cells with certain values. I need to compare each and every cell from that range with each and every cell from a different range of cells. If there are the same cell values, I need to highlight the cell from the second range of cells.

Since the values from the first range are all different from each other, it would be nice if each duplicate cell would have different coulor.

Is this possible to do in excel and if yes, how?

Thank you in advance,

 

[JoeMo]

Duplicates in range2 could be filled with a single color using Conditional Formatting. A VBA subroutine could be written to fill each duplicate with different color.

As an example, suppose range 1 is A2:A20 and range2 is C2:C25, then:

For the former solution you would select all the cells in range2 and go to Home>Conditional Formatting>New Rule>Use a formula. The formula is: =NOT(ISNA(MATCH($C2,$A$2:$A$20,0)))
Then select the color you want for the fill and click OK.

The latter solution (multiple colors) requires that you are willing to enable macros.