HighlightDupesCaseInsensitive.bas

Public Sub HighlightDupesCaseInsensitive()
    Dim Cell As Range
    Dim Delimiter As String
 
    Delimiter = InputBox("Enter the delimiter that separates values in a cell", "Delimiter", ", ")
 
    For Each Cell In Application.Selection
        Call HighlightDupeWordsInCell(Cell, Delimiter, False)
    Next
End Sub
 
Sub HighlightDupeWordsInCell(Cell As Range, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = True)
    Dim text As String
    Dim words() As String
    Dim word As String
    Dim wordIndex, matchCount, positionInText As Integer
 
    If CaseSensitive Then
        words = Split(Cell.Value, Delimiter)
    Else
        words = Split(LCase(Cell.Value), Delimiter)
    End If
 
    For wordIndex = LBound(words) To UBound(words) - 1
        word = words(wordIndex)
        matchCount = 0
 
        For nextWordIndex = wordIndex + 1 To UBound(words)
            If word = words(nextWordIndex) Then
                matchCount = matchCount + 1
            End If
 
        Next nextWordIndex
 
        If matchCount > 0 Then
            text = ""
 
            For Index = LBound(words) To UBound(words)
                text = text & words(Index)
 
                If (words(Index) = word) Then
                    Cell.Characters(Len(text) - Len(word) + 1, Len(word)).Font.Color = vbRed
                End If
 
                text = text & Delimiter
            Next
        End If
    Next wordIndex
End Sub