Update specific characters to different font with VBA

user_12916452 0
Viewed 80

I have an Excel Range A1:C5 merged with the following text:

Strengths ê He will be a strong person and will do well in bowling. Weaknesses ê He is not very smart and will need extra attention.

How can I change just the ê to Wingdings 2 font, so it turns into the appropriate symbol?

The following code will turn the first instance of ê to Wingdings 2 but not the second instance of ê

mySheet.Range("A1").Characters(WorksheetFunction.Find(Chr(234), Range("A1").Value, 1), Len(Chr(234))).font.Name = "Wingdings 2"
vba excel
1 Answers

Since you can pass any start/ length to the Characters object in order to update the font, you can loop through each character in the text and just do that when it matches Chr(234). For example:

Option Explicit

Sub Test()
    FontUpdate Sheet3.Range("A1"), Chr(234), "Wingdings 2"
End Sub

Sub FontUpdate(rngTarget As Range, strFind As String, strFont As String)
    Dim lngIndex As Long
   
    For lngIndex = 1 To Len(rngTarget.Value)
        If Mid(rngTarget.Value, lngIndex, Len(strFind)) = strFind Then
            rngTarget.Characters(lngIndex, Len(strFind)).Font.Name = strFont
        End If
    Next lngIndex
    
End Sub

enter image description here

user_7653950
Related