VBA Code to compare 2 column and find match (including case)

[Vedaanth137]

Hi,

I have 2 columns of Names. Column A has 1034 names and Column B has 4500 names.
I would like to have a code that checks if the names in column A match any names in Column B.

If it finds Exact Match - Make the cell green.
If it finds Match but the case is not matching - make it Blue.
If no matches found - Make it Red.

Regards,
Vedaanth

[HansV]

Select the names in column A. In the following, I will assume that the selection begins in cell A2 and that A2 is the active cell within the selection.
Set the background color to red - this will be the default (no match).

On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISNUMBER(MATCH(A2,$B$2:$B$4500,0))

Here, A2 is the active cell, and $B$2:$B$4500 is the range with names in column B. The $ in $B$2:$B$4500 are important.
Click Format...
Activate the Fill tab.
Select blue, then click OK.
Click OK again to apply the rule.

Repeat the above steps to create a conditional formatting rule, but this time with the formula

=ISNUMBER(MATCH(TRUE,EXACT(A2,$B$2:$B$4500),0))

and with green as color.

[Vedaanth137]

Thank you so much.
I will try this and come back.

[StuartR]

I'm a lot slower than Hans, but here is an alternative solution that you could try...