I have a workbook with multiple worksheets.
Sheet1 (cells B4:B54) has random names in it. Sheets 2, 3, etc. have random names as well, also entered in cells B4:B54, like so:
I need a VBA/macro that does the following:
Please help. Thanks in advance.
Sheet1 (cells B4:B54) has random names in it. Sheets 2, 3, etc. have random names as well, also entered in cells B4:B54, like so:
I need a VBA/macro that does the following:
- If ANY names on Sheet1 are found on the other sheets, then go to the other sheets and highlight the duplicates in green.
- NOTHING is supposed to be highlighted on Sheet1 itself: Sheet1 is purely for comparison purposes.
Please help. Thanks in advance.

VBA Code:
'THIS CODE DOES NOT WORK; IT HIGHLIGHTS EVERY DUP EVEN ON SHEET1
'CODE TAKEN FROM: https://www.reddit.com/r/excel/comments/3ky92o/how_to_highlight_duplicates_across_an_entire/
Sub ColorDuplicates()
Dim w As Integer
Dim c As Range
Dim z As Integer
Application.ScreenUpdating = False
For Each
For w = 1 To ThisWorkbook.Worksheets.Count
For Each c In Sheets(w).Range("B4:B34" & Sheets(w).UsedRange.Rows.Count)
For z = w + 1 To ThisWorkbook.Worksheets.Count
If Not Sheets(z).Range("B4:B34" & Sheets(z).UsedRange.Rows.Count).Find(c, LookAt:=xlWhole) Is Nothing Then
ColorAll c, Sheets(z)
ColorAll c, Sheets(w)
End If
Next z
Next c
Next w
Application.ScreenUpdating = True
End Sub
Sub ColorAll(Value As Variant, WS As Worksheet)
Dim Finder As Range
Dim FirstAddress
Set Finder = WS.Range("B4:B34" & WS.UsedRange.Rows.Count).Find(Value, LookAt:=xlWhole)
If Not Finder Is Nothing Then
FirstAddress = Finder.Address
Do
Finder.Font.Color = RGB(0, 176, 80)
Set Finder = Finder.FindNext(Finder)
Loop While Not Finder Is Nothing And Finder.Address <> FirstAddress
End If
End Sub
Sub ClearColors()
For Each w In ThisWorkbook.Worksheets
w.Range("B4:B34" & w.UsedRange.Rows.CountLarge).Interior.ColorIndex = xlNone
Next w
End Sub