Here is example code that highlights specific words or phrases in a column of values.
In my file, I listed the keywords or phrases in column A starting in cell A1 while the
text to be searched and partially formatted in bold font resided in column B, starting
in cell B1.
Sub Highlight_Keywords()
'apply bold font to all found keywords or phrases, including multiple targets
'found in the same string of text, for whole words only.
'macro ignores case and does not format keywords if word is punctuated or is
'preceeded or followed by other characters such as a hyphen or parenthesis.
Dim i As Integer, pos As Integer, intLength As Integer
Dim lastDataRow As Long, keyWordCount As Long, j As Long
Dim keyWord As String
Application.ScreenUpdating = False
With Sheets("Sheet1")
keyWordCount = .Range("A65536").End(xlUp).Row
lastDataRow = .Range("B65536").End(xlUp).Row
For i = 1 To keyWordCount
'adding a leading and trailing space will locate whole keywords at begining or end of text
keyWord = " " & UCase(Trim(.Cells(i, 1).Value)) & " "
intLength = Len(Trim(keyWord))
For j = 1 To lastDataRow
pos = InStr((pos + 1), " " & UCase(.Cells(j, 2).Value) & " ", keyWord)
While pos > 0
If pos = 0 Then Exit For
.Cells(j, 2).Select
ActiveCell.Characters(Start:=pos, Length:=intLength).Font.FontStyle = "Bold"
pos = InStr(pos + Len(keyWord), " " & UCase(.Cells(j, 2).Value) & " ", keyWord)
Wend
Next
Next
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub
Bookmarks