How to automate check for duplicates in multiple lists in multiple sheets in the same workbook?
I’ve managed to make it work semi-automatically by having a macro for each comparison that I need. That means a macro for finding duplicate values between:
Worksheets(“All”).Range(“Annie”) and Worksheets(“Annie”).Range(“A:A”)
Worksheets(“All”).Range(“Benny”) and Worksheets(“Benny”).Range(“A:A”)
(Annie’s been to Sweden and Kenya – therefore those countries are marked red by the macro)
(Benny’s been to Russia and Spain)
(And so forth)
It would be much more efficient to make just ONE code, that does this for all columns that has values in Worksheets(“All”). I need to be able to have at least 50 columns with names (Annie, Benny, Charlie, Danny, Emma, Fabian, etc., etc..)
Is this possible?
Here is my code, so far (for “Benny”):
Sub findDuplicatesBenny() Dim rng1, rng2, cell1, cell2 As Range Set rng1 = Worksheets("All").Range("Benny") Set rng2 = Worksheets("Benny").Range("A:A") For Each cell1 In rng1 If IsEmpty(cell1.Value) Then Exit For For Each cell2 In rng2 If IsEmpty(cell2.Value) Then Exit For If cell1.Value = cell2.Value Then cell1.Interior.ColorIndex = 22 cell1.Interior.Pattern = xlSolid End If Next cell2 Next cell1 End Sub
Any help will be much appreciated!