Compare name list (First name, last name) on two different sheets and highlight

I have a workbook with two sheets with two columns on each which need to be compared and highlight any duplicates on Sheet 2

Example:

Sheet 1

First Name Last Name
Joe Bloggs
Harry Houdini
Tony Stark
Batman Glooney

Sheet 2

First Name Last Name
Harry Houdini
Peter Pan
Joe Graham
Joe Bloggs

On Sheet 2, put this formula in C2:

=A2 & B2

then copy down.

Then in C2 on Sheet 1 you can have this formula:

=IF(COUNTIF(Sheet2!C:C,A2&B2)>0,"Duplicated","not on Sheet2")

and copy this down.

Hope this helps.

Pete

Can we get it to highlight

Select all the cells in Columns A and B on Sheet1, starting with A2, and click on Conditional Formatting | New Rule, then choose Use a Formula from the bottom of the list. Put this formula in the dialogue box that pops up:

=$C2="Duplicated"

then click on the Format button, then the Fill tab and choose your colour. Then click on OK twice and Excel will adjust the cell references to suit the selected cells.

Hope this helps.

Pete