0

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?

Community
  • 1
  • 1
GoneFishin
  • 49
  • 8
  • https://stackoverflow.com/a/11672813/6706419 – urdearboy May 18 '20 at 20:47
  • @urdearboy Thanks for the link! It looks like Jack BeNimble hit my question dead on, but their code is really hard to decipher. I'm still looking for someone (or another link) to more clearly help me resolve this sub issue. – GoneFishin May 18 '20 at 21:58
  • Refer [this question](https://stackoverflow.com/q/60357683/9808063) for hint. – Naresh May 18 '20 at 22:01

0 Answers0