Hi,
I have this macro that does what I want it too.
Sub findDuplicates()
' code to find duplicates in 2 different worksheets
Dim rng1, rng2, cell1, cell2 As Range
' 4 ranges have been defined
Set rng1 = Worksheets("Sheet1").Range("A:A") 'rng1 defines the existing data in column A and worksheet1
Set rng2 = Worksheets("Sheet2").Range("C:C") 'rng2 defines the imported data in column C and worksheet2
For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For
'check for empty rows. If true then exit the program
For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For
If cell1.Value = cell2.Value Then
'compare data in cell1 and cell2 and then format if they have equal values.
cell1.Font.Bold = True
cell1.Font.ColorIndex = 2
cell1.Interior.ColorIndex = 3
cell1.Interior.Pattern = xlSolid
cell2.Font.Bold = True
cell2.Font.ColorIndex = 2
cell2.Interior.ColorIndex = 3
cell2.Interior.Pattern = xlSolid
End If
'run the looping process
Next cell2
Next cell1
End Sub
Except I would like it to go through all worksheets, at the moment I change the Sheet2 part of the macro in this part to the name of the other worksheet: - "Set rng2 = Worksheets("Sheet2").Range("C:C")"
Can anyone please tell me how I can change this to cycle through all Worksheets in the Book without me manually having to name each sheet and have it perform the same function? I have attached a sample worksheet with the macro, as you can see it only does Sheet A, I want it to do both Sheet A AND Sheet B if possible?
Thanks!
Sample Data.xlsm
Bookmarks