excelvbaexcel-2010

Hightlighting String from substring


In excel, how do I highlight parts of a string in column A, based on substring(s) from column B? The substrings in column B can be in any order, not necessarily follow the order of string from column A

For example:

enter image description here

String Word
I like dog. dog
I like cat. cat
Parrot, cat, dog Parrot, dog
Mouse, bird, dog bird
soccer, football, baseball baseball, soccer

Solution

  • This will loop through the cells and compare the two strings, splitting any comma delimited lists in B into an array and comparing the elements to A.

        Dim i As Long
        Dim lr As Long
        
        Dim wordarr As Variant
        Dim element As Variant
        Dim target As Long
        
        With Sheets(1)
            lr = .Cells(.Rows.Count, 1).End(xlUp).Row
            For i = 2 To lr
                wordarr = Split(.Cells(i, 2).Value, ",")
                For Each element In wordarr
                    target = InStr(1, .Cells(i, 1).Value, element)
                    If target > 0 Then
                        .Cells(i, 1).Characters(target, Len(element)).Font.Color = RGB(255, 0, 0)
                    End If
                Next
            Next i
        End With