Change specific text font color in shapes

[zinah]

Hi,

I have shapes that have text and I need to change the text font color of any "please change color to blue - example" if it's found in shapes. How can I do that in a macro?

 

[DanteAmor]

There is no file to download.

Try whit my file test

https://www.dropbox.com/s/ekyj66rbrtdpxcc/shape change specific text.xlsm?dl=0

 

[zinah]

I really can't thank you enough for the amazing help you offered, I used your test file and it worked perfectly fine. Only one thing I need to make this file ready, is the other condition which is populating the text in other shapes: " If InStr(1, shp.Name, "Goal_" & i & "_Out") > 0 Then"
How can I make same condition work for these shapes?

   Set aSht = ActiveSheet    Set rSht = Sheets("Role Scorecard")
    rSht.Activate




Dim oCnt As Long, o As Long '>>>set the limit of objectives count
Dim shp As Shape
Dim ObjRng As Range, MetRng As Range, catRng As Range
Dim objLbl As Shape, outLbl As Shape, catLbl As Shape
Dim prgTxt As String, prglbl As String


oCnt = 100
If oCnt > 5 Then oCnt = 5


    Set ObjRng = Sheets("ref.").[Pop_ObjRng]
    Set MetRng = Sheets("ref.").[Pop_OutRng]
    Set numrng = Sheets("ref.").[NumRange]
    Set catRng = Sheets("ref.").[CatRange]
    Set PrgRng = Sheets("ref.").[ProgRange]
    Set ModRng = Sheets("ref.").[Mod_Date]
    wString = "more detail available in SF"
    l = Len(wString)
    For o = 1 To oCnt
        For lbl = ObjRng.Row To (ObjRng.Row + ObjRng.Rows.Count - 1)
            i = i + 1
            For Each shp In ActiveSheet.Shapes
                If InStr(1, shp.Name, "Goal_" & i & "_Obj") > 0 Then
                    With shp
                        wtext = Sheets("ref.").Cells(lbl, numrng.Column) & " " & _
                            "Objective: " & "Last modified" & " " & _
                            Sheets("ref.").Cells(lbl, ModRng.Column).Value & Chr(10) & _
                            Sheets("ref.").Cells(lbl, ObjRng.Column).Value
                        .TextFrame2.TextRange.Characters.Text = wtext
                        .TextFrame2.TextRange.Font.Bold = msoFalse
                        '.TextFrame.Characters(InStr(1, .TextFrame2.TextRange.Characters.Text, ObjRng), Len(ObjRng)).Font.Bold = True
                        .TextFrame2.TextRange.Characters(1, 14).Font.Bold = msoCTrue
                        n = InStr(1, wtext, wString)
                        If n > 0 Then
                            shp.TextFrame.Characters(Start:=n, Length:=l).Font.ColorIndex = 5
                        End If
                    End With
                End If
    
    If InStr(1, shp.Name, "Goal_" & i & "_Out") > 0 Then
        With shp
        .TextFrame2.TextRange.Characters.Text = "Metrics/Outcomes: " & Chr(10) & Sheets("ref.").Cells(lbl, MetRng.Column).Value
        .TextFrame2.TextRange.Font.Bold = msoFalse
        .TextFrame2.TextRange.Characters(1, 17).Font.Bold = msoCTrue
        End With
    End If

This is the second condition that populate the text in all the shapes named "Goal_"& i &"_Out"

    If InStr(1, shp.Name, "Goal_" & i & "_Out") > 0 Then
        With shp
        .TextFrame2.TextRange.Characters.Text = "Metrics/Outcomes: " & Chr(10) & Sheets("ref.").Cells(lbl, MetRng.Column).Value
        .TextFrame2.TextRange.Font.Bold = msoFalse
        .TextFrame2.TextRange.Characters(1, 17).Font.Bold = msoCTrue
        End With

 

[DanteAmor]

Try this

    [COLOR=#0000cd]wString [/COLOR]= "more detail available in SF"
    [B][COLOR=#0000cd]l [/COLOR][/B]= Len(wString)


    If InStr(1, shp.Name, "Goal_" & i & "_Out") > 0 Then
        With shp

         [COLOR=#ff0000]wtext [/COLOR]= "Metrics/Outcomes: " & Chr(10) & Sheets("ref.").Cells(lbl, MetRng.Column).Value


        .TextFrame2.TextRange.Characters.Text = [COLOR=#ff0000]wtext[/COLOR]
        .TextFrame2.TextRange.Font.Bold = msoFalse
        .TextFrame2.TextRange.Characters(1, 17).Font.Bold = msoCTrue

         n = InStr(1, wtext, [COLOR=#0000cd]wString[/COLOR])
         If n > 0 Then
               .TextFrame.Characters(Start:=n, Length:=[B][COLOR=#0000cd]l[/COLOR][/B]).Font.ColorIndex = 5
         End If


        End With

 

[zinah]

THANK YOU!!! really appreciate your kind support and time you spent to help me with these macros.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.