Loop through cells and then through all worksheets

[jeffreybrown]

Not sure what is missing here, but this will only highlight duplicates on the active sheet and won't cycle thru all worksheets in the workbook.

Sub FindDups()
Dim i As Range
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each i In Range("A1:E3")
            If Application.WorksheetFunction.CountIf(Range("A1:E3"), i) > 1 Then
                i.Interior.ColorIndex = 33
            End If
        Next i
    Next ws
End Sub

[DonkeyOte]

You need to qualify the Range being iterated as belonging to the Worksheet object being iterated (eg ws), ie:

For Each i In ws.Range("A1:E3")

(same would hold true in the COUNTIF)

You may however prefer to use a With statement...

[jeffreybrown]

You may however prefer to use a With statement...

Thanks Luke...As usual spot on...

I'm not quite sure what you mean by the With statement, but would it have advantages over the ws.range?