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:
String | Word |
---|---|
I like dog. | dog |
I like cat. | cat |
Parrot, cat, dog | Parrot, dog |
Mouse, bird, dog | bird |
soccer, football, baseball | baseball, soccer |
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