使用Scripting.Dictionary实现。

Sub How_to_highlight_duplicate_word_in_a_column_in_spreadsheet_ignoring_text_case_and_Punctuation()
    Dim cln As Range, ws As Worksheet, c As Range, cv As String, cCollection As New VBA.Collection, key, colr As Long
    Dim cDict As New Scripting.Dictionary
    'Dim cDict As Object
    'Set cDict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    Set cln = ws.UsedRange.Columns(3) 'for test
    For Each c In cln.Cells
        cv = c.Value
        If cv <> vbNullString Then
            cv = VBA.StrConv(cv, vbLowerCase)
            RemovePunctuation cv
            If cDict.Exists(cv) Then
                Set cCollection = cDict(cv)
                cCollection.Add c
            Else
                Set cCollection = Nothing
                cCollection.Add c
                cDict.Add cv, cCollection
            End If
        End If
    Next c

    For Each key In cDict.Keys
        Set cCollection = cDict(key)
        If cCollection.Count > 1 Then
            colr = RGB(Int(Rnd * 256), Int(Rnd * 256), Int(Rnd * 256))
            For Each c In cCollection
                ComplementaryColors c, colr
'                With c.Interior
'            '        .Pattern = xlSolid
'            '        .PatternColorIndex = xlAutomatic
'                    .Color = colr
'            '        .TintAndShade = 0
'            '        .PatternTintAndShade = 0
'                End With
            Next c
        End If
    Next key
End Sub

Function RemovePunctuation(str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Pattern = "[^A-Z0-9 ]"
        '.Pattern = "[^\w\s]"
        .IgnoreCase = True
        .Global = True
        str = .Replace(str, "")
    End With
    RemovePunctuation = str
End Function

Sub ComplementaryColors(cell As Range, color As Long)
    With cell.Font
        .color = color
'        .color = RGB(255 - color Mod 256, 255 - (color \ 256) Mod 256, 255 - color \ 65536)
    End With
    With cell.Interior
        .color = RGB(255 - color Mod 256, 255 - (color \ 256) Mod 256, 255 - color \ 65536)
    End With
    
End Sub