I'm writing a Sub that finds all instances of the word "uM" in a single cell and change the font to blue. I want to learn more about loops, and this seems like a good application. My code only changes the font color for the first incidence of the word "uM," but I want it to change all instances in the cell string. The word "uM" appears in many places throughout the sheet, so I need it to only change the font color in this cell. Here is a picture of the cell.
Here is my code:
Sub Insert_Warning_Pfizer()
Dim WS As Worksheet
Dim Attention As Range
Dim uM As String
Dim sPos As Long
Dim sLen As Long
Dim n As Long
Set WS = ActiveWorkbook.ActiveSheet
uM = "uM"
Set Attention = WS.Cells.Find(What:="Attention", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Find Attention range
If Not Attention Is Nothing Then
With Attention
For n = 1 To Len(uM)
sPos = InStr(1, Attention.Value, uM)
sLen = Len(uM)
Attention.Characters(Start:=sPos, Length:=sLen).Font.ColorIndex = 42
Next n
End With
End If
End Sub
Any suggestions on how to change the font for all instances of "uM"? Is there a way to loop through all words in a single cell string, or am I off-track?