Excel VBA: Change Font Color for Part of Text: 3 Methods

Method 1 – Change the Font Color for a Specific Part of Text Using VBA in Excel

1.1 Use of the Font.Color Property

Along with the above property, to color a partial text we need to use the Font.Color property of Excel. We can use different color constants defined by VBA Excel in our code to color text. The constants are- vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan and vbWhite.

Let’s copy and paste the following code to see the change in font color for a part of the text.

Public Sub ChangeFontColor()
    Range("B4").Characters().Font.Color = vbBlue
    Range("B5").Characters(1).Font.Color = vbRed
    Range("B6").Characters(7).Font.Color = vbGreen
    Range("B7").Characters(8, 4).Font.Color = vbYellow
    Range("B8").Characters(15, 6).Font.Color = vbMagenta
    Range("B9").Characters(, 6).Font.Color = vbBlack
    Range("B10").Characters(, 11).Font.Color = vbCyan
End Sub

Press F5 to run the code and the output is in the following screenshot.

1.2 Apply the RGB Color System

Apply the RGB function, which gives us the option to vary the color palette.
Syntax of RGB function is:

RGB(Red As Integer, Green As Integer, Blue As Integer) As Long

This function takes three arguments as integers whose value ranges from 1 to 255. That means we are given 255*255*255 = 16, 581, 375 color combinations to put to color a text.

Copy and paste the following code to change the font color for a part of the text with an RGB color palette.

Public Sub ChangeFontColor()
    Range("B4").Characters().Font.Color = RGB(0, 0, 0)
    Range("B5").Characters(1).Font.Color = RGB(255, 0, 0)
    Range("B6").Characters(7).Font.Color = RGB(0, 255, 0)
    Range("B7").Characters(8, 4).Font.Color = RGB(0, 0, 255)
    Range("B8").Characters(15, 6).Font.Color = RGB(255, 255, 0)
    Range("B9").Characters(, 6).Font.Color = RGB(0, 255, 255)
    Range("B10").Characters(, 11).Font.Color = RGB(255, 255, 255)
End Sub

1.3 Perform the ColorIndex Property

Moreover, we can use the ColorIndex property to set a font color easily. The following screenshot shows the color-index values in the default color palette.

Run the code in the visual basic editor to change the font color for part of the text.

Public Sub ChangeFontColor()
    Range("B4").Characters().Font.ColorIndex = 1
    Range("B5").Characters(1).Font.ColorIndex = 3
    Range("B6").Characters(7).Font.ColorIndex = 8
    Range("B7").Characters(8, 4).Font.ColorIndex = 10
    Range("B8").Characters(15, 6).Font.ColorIndex = 5
    Range("B9").Characters(, 6).Font.ColorIndex = 27
    Range("B10").Characters(, 11).Font.ColorIndex = 26
End Sub

Method 2 – Run a VBA Code to Find and Change the Font Color of a Targeted Part of a Text

In cells B4:B6, we have some text that contains the word “Color” a few times.

We want to change the font color of these words in this illustration. Let’s copy and paste the following code into the Visual Basic editor.

Public Sub ChangefontColor()
    Set TextRange = Range("B4:B6")
    partOfText = "Color"
    fontColor = 1
    For Each part In TextRange
        lenOfPart = Len(part)
        lenPartOfText = Len(partOfText)
        For i = 1 To lenOfPart
            tempStr = Mid(part, i, lenPartOfText)
            If tempStr = partOfText Then
                part.Characters(Start:=i, Length:=lenPartOfText).Font.ColorIndex = fontColor
            End If
        Next i
    Next part
End Sub

In the code,

• Set the TextRange variable to B4:B6.
• Then the partOfText variable is set to Color, as we want to change this part of the text to change it’s color.
• We set the color index value to 5 that’ll change the font color to Blue.

Run the code by pressing F5 and the output is in the following screenshot.

Method 3 – Change Font Color for a Part of Text Between Two Special Characters Using VBA in Excel    

We’ll show how to change the font color of a part of the text surrounded by specific characters or strings in a large text. Let’s say we have the following lines of text in cells B4:B6 having the word “Colors” multiple times in it. We want to change the font of only those words enclosed by two brackets i.e., (Color).

Run the code in the Visual Basic editor.

Public Sub ChangeFontColor()
    Set TextRange = Range("B4:B6")
    strCharacter = "("
    endCharacter = ")"
    fontColor = 5
    On Error Resume Next
    For Each part In TextRange
        Dim strCharacterArr(), endCharacterArr()
            For i = 1 To Len(part)
                tempStr = Mid(part, i, 1)
                If tempStr = strCharacter Then
                    Count = Count + 1
                    ReDim Preserve strCharacterArr(Count)
                    strCharacterArr(Count) = i
                End If
            Next i
            For j = 1 To Len(part)
                tempStr2 = Mid(part, j, 1)
                If tempStr2 = endCharacter Then
                    count2 = count2 + 1
                    ReDim Preserve endCharacterArr(count2)
                    endCharacterArr(count2) = j
                End If
            Next j
            For k = 1 To Count
                If endCharacterArr(k) > strCharacterArr(k) Then
                    part.Characters(Start:=strCharacterArr(k), Length:=endCharacterArr(k) - strCharacterArr(k) + 1).Font.ColorIndex = fontColor
                End If
            Next k
        Erase strCharacterArr()
        Erase endCharacterArr()
    Next part
End Sub

The output is-

Things to Remember

• While writing code with multiple lines, we need to be careful in maintaining the right sequence.
• It is important to select the text range and targeted words cautiously. Once we run the code, we cannot revert it. For information, the VBA code doesn’t record the work history.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.