Hi,

I want to search column C, and hi-light all duplicate values. But I only want to compare cells which contain the character _

So if in C column , I have the following:

apple <-----(ignore this dupe)
dog
dog_a
cat
apple <----- (ignore this dupe)
cat_a
dog_a

I only want it to find dupes with _. Ignore any other dupes

I have this code which is not working. Nothing happens:


Public Sub FindDuplicates()

    Dim MyRange As Range
    Dim Cell As Range
    Dim CountOfDuplicate As Long

    Set MyRange = ActiveSheet.Range("C:C").CurrentRegion

    For Each Cell In MyRange
        CountOfDuplicate = Application.WorksheetFunction.CountIf(MyRange, "=" & Cell)
        If CountOfDuplicate > 1 And InStr(1, Cell, "_") > 0 Then
            Cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next Cell

End Sub