find duplicates in excel

Hi,

I want a formula to find duplicate values between two columns. In the following example Anna & Anna2 are require to get same color.

Col A Col B
Anna Anna2
Ann2 Anna3
Anna Anna4
Anna1 Anna3

Thanks
Anna

Highlight the data in column A (presumably starting from A1), then click on Conditional Formatting | New Rule | Use a formula... (from the bottom of the list), then put this formula in the dialogue box:

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

Click on the Format button | Fill tab and choose your colour, then click OK twice to exit the dialogue box.

Now highlight the data in column B and repeat, using this formula:

=ISNUMBER(MATCH(B1,A:A,0))

Hope this helps.

Pete