Many thanks to those who manage this forum - you provide a great service. Here are the details of my issue:
System: Mac OS X, Macbook pro, Office 2011
Spreadsheet: 5 columns, variable rows but always a header row. Column C holds key words. Column E holds concatenated text that includes at least one instance of the key word in Column C.
Objective: Search in Column E for the key word that is in Column C and format all instances of the key word only in Column E with bold, italic and underline.
The sub below ran OK several times but quit working now and will not format the words.
Any and all help would be greatly appreciated. Thank you.
Sub Logos_Key_Word()
'Objective of sub is to search a cell for instances of a key words and apply formatting to those words with bold, italic and underline.
'Key words are in column C and text to be searched (and where key words are to be formatted) is in column E.
Dim PosF As Integer, aLen As Integer 'PosF is the position of the first letter of the key word and aLen is the length of the key word.
Dim PosL As Integer, PosT As Integer 'PosL is the position of the last letter of the key word and PosT is used to test that an instance of the key word is present.
Dim aWord As String 'aWord is the key word from the current row in column C.
Dim lRow As Long
Dim i As Integer
'Count rows with data.
lRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lRow 'Row 1 is a header so start in row 2.
Cells(i, 5).Select
aWord = ActiveCell.Offset(0, -2).Text 'Get key word from column C.
aLen = Len(aWord)
PosF = InStr(Cells(i, 5), aWord) 'Find the position of the first letter of the key word.
PosT = 0
Do While PosF > PosT 'PosF should be greater than PosT as long as an instance of the key word is found.
With Cells(i, 5).Characters(PosF, aLen).Font 'Apply the formatting to the characters of the key word.
.FontStyle = "Bold"
.Underline = True
.Italic = True
End With
PosL = PosF + aLen 'Adjust position variables to check for another instance of the key word.
PosT = PosT + PosL
PosF = InStr(PosT, Cells(i, 5), aWord, vbTextcompare)
Loop
Next i
End Sub
Bookmarks