Finding Duplicates across multiple worksheets in Excel via VBA

[AlanaH]

Hello!

I was hoping someone could help me out!

I have an excel workbook that consists of multiple e-mails that I use for the purpose of a distribution list.

I need a way to highlight the duplicates across the various worksheets. There are 12 worksheets that have e-mail addresses all in column B. The range in the column varies.

Thanks!

 

[NdNoviceHlp]

Hi AlanaH and Welcome to the Board! U can trial this. HTH. Dave

Public Sub CompareShtsB()
Dim RowCnt As Double, RowCnt2 As Double, Sht As Worksheet
Dim LastRow As Double, LastRow2 As Double, Sht2 As Worksheet
'*Colours similiar cells cyan in "B" from all sheets
On Error GoTo FixEr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Sht In ThisWorkbook.Worksheets
LastRow = Sheets(Sht.Name).Range("B" & Rows.Count).End(xlUp).Row
For Each Sht2 In ThisWorkbook.Worksheets
If Sht.Name <> Sht2.Name Then
LastRow2 = Sheets(Sht2.Name).Range("B" & Rows.Count).End(xlUp).Row
For RowCnt = 1 To LastRow
For RowCnt2 = 1 To LastRow2 'loop sht rows
'don't search error cells
If Not IsError(Sheets(Sht.Name).Cells(RowCnt, "B")) Then
'don't search blank cells
If Sheets(Sht.Name).Cells(RowCnt, "B") <> vbNullString Then
'color similiar cells in blue
If LCase(Sheets(Sht.Name).Cells(RowCnt, "B")) = _
       LCase(Sheets(Sht2.Name).Cells(RowCnt2, "B")) Then
Sheets(Sht.Name).Cells(RowCnt, "B").Interior.Color = vbCyan 'blue
'***To return to normal, comment out above line and remove comment below
'Sheets(Sht.Name).Cells(RowCnt, "B").Interior.Color = vbWhite 'white
Sheets(Sht.Name).Cells(RowCnt, "B").Borders.LineStyle = xlContinuous
Sheets(Sht.Name).Cells(RowCnt, "B").Borders.Color = RGB(170, 170, 170) 'grey
End If
End If
End If
Next RowCnt2
Next RowCnt
End If
Next Sht2
Next Sht

FixEr:
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Error"
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[AlanaH]

Thank you so much Dave!

Lifesaver!!

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave