Hi - I'm using Excel 2003. Is it possible to find duplicates spread over 2 worksheets?
I have one w/s with a list of names, the other has some of those names plus some which are new. Can I somehow finish up with the names which are on both sheets highlighted in some way on the second sheet?
I know I can cut, past, merge and sort, but what I'd really like to do is for a repeated name on the second sheet to become, say, red, if it appears on sheet 1.
Try a macro. ALT + F11 to open the Visual Basic Editor. Insert > Module then copy and paste in:
Sub CheckDups() Dim NewSht As Worksheet, OldSht As Worksheet, NewLastRow, iRow, Found As Range Set NewSht = Sheets("Entries2007") Set OldSht = Sheets("Entries2006") NewLastRow = NewSht.Cells(Cells.Rows.Count, 4).End(xlUp).Row For iRow = 2 To NewLastRow Set Found = OldSht.Columns(4).Find(what:=NewSht.Cells(iRow, 4).Value, lookat:=xlWhole) If Not Found Is Nothing Then NewSht.Cells(iRow, 4).Font.ColorIndex = 3 Next iRow End Sub
Change the sheet names to suit and change the 4 to matrch the column that you want to check (1=A, 2=B, 3=C, 4=D etc.).