Comments
- If you want "yello" to be highlighted as well as "yellow" you would need to include them both in the second column list (subject to my next point)
- In your first post, you talked about "words" being highlighted. The suggested code does not account for "words" but simply text strings. For example, with your 3 'List' words in post 1, this happens would happen.
Your scarring has reduced
becomes
Your s
carring has
reduced
Perhaps that is what you want and you didn't mean "words", but if you did, see my alternate code below.
- The suggested code only operates on as many rows in column A as there are in column B. That is, If 10 rows in column A and 3 rows in column B, the last 7 rows in col A will not be checked.
- The suggested code only highlights the first occurrence of a string. For example
My car is red but your car is blue
becomes
My
car is
red but your car is blue
ie The second "car" is not highlighted
My suggestion, to process all rows in column A and only look for "word" matches is
Code:
Sub HighlightWords()
Dim RX As Object, Mtchs As Object
Dim itm As Variant
Dim c As Range
Set RX = CreateObject("VBScript.RegExp")
RX.Global = True
RX.IgnoreCase = True
RX.Pattern = "\b(" & Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp)).Value), "|") & ")\b"
Application.ScreenUpdating = False
Columns("A").Font.ColorIndex = xlAutomatic
Columns("A").Font.Bold = False
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Mtchs = RX.Execute(c.Value)
For Each itm In Mtchs
With c.Characters(Start:=itm.firstindex + 1, Length:=itm.Length)
.Font.Color = vbRed
.Font.Bold = True
End With
Next itm
Next c
Application.ScreenUpdating = True
End Sub