Change font color based on partial text

[mickyh]

Good morning
I'm looking for suggestions on how I might change the font color based on the first word (or two) so that I can highlight cells that start "Optional" or "Provisional Sum" in red. The first problem I'm having is searching with partial text. (not sure if my code beyond that works or not)?

Sub FindStringSOW()

Dim c As Range
    Dim firstAddress As String

    With ActiveSheet.Range("C1:C100")
        Set c = .Find("Optional:** ", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.font = vbRed
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With

End Sub

 

[Peter_SSs]

Try this instead

Sub FindStringSOW_v2()
  Dim c As Range
  Dim firstAddress As String
  
  With ActiveSheet.Range("C1:C100")
    Set c = .Find("Optional*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
      firstAddress = c.Address
      Do
        c.Font.Color = vbRed
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    Set c = .Find("Provisional Sum*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
      firstAddress = c.Address
      Do
        c.Font.Color = vbRed
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
End Sub

 

[Peter_SSs]

.. or another approach that you could try. (Assumes a heading row in row 1)

Sub FindStringSOW_v3()
  With Range("C1:C100")
    .AutoFilter Field:=1, Criteria1:="Optional*", Operator:=xlOr, Criteria2:="Provisional Sum*"
    If .SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).Font.Color = vbRed
    .AutoFilter
  End With
End Sub

 

[mickyh]

Thanks, Peter
That works perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Did you try the second, more compact, form, too?

 

[mickyh]

Thanks again Peter
That also works perfectly and is the solution that I'm using. I also find it easier to follow. ? I've now marked that as the solution but both worked perfectly.