VBA to Compare two sheets if one contains same data

[henry_90lx]

Been coming here for a long time to get all my excel help, never have had to post before as every question I have had has already been answered in a post, that is up untill now.

Have:

Sheet1, column B starting on line 2 and going to line 8500 = Last, First Middle (all in one cell)

Sheet2, column A starting on Line 1 and going to about line 500 = Last (in one cell)

What I need to do is take all the last names on Sheet2 and compare them to all the names on Sheet1.

If the Last name I have on sheet2 matches a last name on sheet1 then I need it to be flagged on Sheet1 some how (Line highlated or text turned red)

The soultion I had found that did not work were do to the comparison looking for exact match. i.e. Sheet1 had the Name: Smith, Bob J and Sheet2 had the name: Smith it would not return it as match, do the the cells not being exact. I would need this example to return as a match.

As always thanks for the help.

 

[JoeMo]

Conditional formatting would be ideal, but can't be used across sheets. If you can copy and paste the last names form Sheet2 into a helper column in Sheet1 then you could use the following CF on the range B2:B8500 on Sheet1:

=ISNUMBER(MATCH(LEFT(B2,SEARCH(",",B2)-1),$C$1:$C$500,0))

Here I've assumed that you inserted a new column (C) after the list in column B of Sheet1. You can set the CF, for example to fill all matching cells with a color.

 

[henry_90lx]

Outstanding. Thanks for the help.