Excel VBA Duplicate operation Not Repeating over multiple sheets

[weepingpea]

Hi everyone,

I adapted this macro that allows me to highlight duplicates in column A of every sheet in my workbook. There are 5 sheets. When I press F8 and have the sheet open, the macro successfully highlights the duplicates. However, when I run F5, it does not work, or sometimes only works for one of the sheets.

I work in PC Excel 2010. Could you see what is wrong with this code?

Many thanks for your help!

weepingpea

Sub HLDupeswithinSheets()
    Dim myColumn As Long
    Dim i As Integer
    Dim columnCount As Long
    Dim lastRow As Long
    Dim dupeColor As Long
    Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet, w4 As Worksheet, w5 As Worksheet
    
    Set w1 = ThisWorkbook.Sheets("1_InProgress")
    Set w2 = ThisWorkbook.Sheets("2_Prospects")
    Set w3 = ThisWorkbook.Sheets("3_CVReview")
    Set w4 = ThisWorkbook.Sheets("4_Events")
    Set w5 = ThisWorkbook.Sheets("5_Others")
    
    columnCount = 1
    dupeColor = 13551615


With w1
    For i = 1 To columnCount
        lastRow = w1.Cells(w1.Rows.Count, i).End(xlUp).Row
        Call HighlightDupesInRange(dupeColor, Cells(1, i).Resize(lastRow, 1))
        dupeColor = dupeColor + 15
    Next i
End With
With w2
For i = 1 To columnCount
        lastRow = w2.Cells(w2.Rows.Count, i).End(xlUp).Row
        Call HighlightDupesInRange(dupeColor, Cells(1, i).Resize(lastRow, 1))
        dupeColor = dupeColor + 15
Next i
End With
With w3
For i = 1 To columnCount
        lastRow = w3.Cells(w3.Rows.Count, i).End(xlUp).Row
        Call HighlightDupesInRange(dupeColor, Cells(1, i).Resize(lastRow, 1))
        dupeColor = dupeColor + 15
Next i
End With
With w4
For i = 1 To columnCount
        lastRow = w4.Cells(w4.Rows.Count, i).End(xlUp).Row
        Call HighlightDupesInRange(dupeColor, Cells(1, i).Resize(lastRow, 1))
        dupeColor = dupeColor + 15
Next i
End With
With w5
For i = 1 To columnCount
        lastRow = w5.Cells(w5.Rows.Count, i).End(xlUp).Row
        Call HighlightDupesInRange(dupeColor, Cells(1, i).Resize(lastRow, 1))
        dupeColor = dupeColor + 15
Next i
End With
End Sub


Sub HighlightDupesInRange(cellColor As Long, rng As Range)
dupeColor = 13551615
    With rng
        '.FormatConditions.Delete
        .FormatConditions.AddUniqueValues
        .FormatConditions(1).DupeUnique = xlDuplicate
        .FormatConditions(1).Interior.Color = dupeColor
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

 

[Comfy]

Make sure you pqass the worksheet to your HighlightDupes routine.

Call HighlightDupesInRange(dupeColor, w1.Cells(1, i).Resize(lastRow, 1))

Otherwise Excel will guess and just use the activesheet.