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

This article illustrates how to change the font color for part of a text in Excel using VBA code. We are going to use Excel’s built-in property and color systems in our examples. Let’s dive into the examples to get a clear understanding of the examples with the VBA codes.

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

In this section, we will demonstrate 3 effective examples of changing font color for part of text using VBA Macro codes.

Introduction to the Range.Characters Property of VBA Excel

In the following examples, we’re going to use the Range.Characters property of Excel to change the font color for a part of the text. This property returns a characters object that allows us to modify any part of a text string. The syntax of the Range.Characters property is-

expression.characters(start, length)

Where the expression represents a Range object i.e., we need to specify the cell reference that holds the text string.

start- this argument returns the first character of a text string based on the value. For example, if the argument value is 5, it’ll return the fifth character of a text string. If the value is 1 or omitted, it returns a range of characters that start with the first character of the text string.

length- this argument specifies the number of characters to return after the first character (defined by the start argument). But in case the argument is omitted, it’ll return all the remaining characters after the start character.

To change the font color for part of the text, we’ll use several VBA functions and properties in our code. The following section describes how to open and write code in the Visual Basic Editor.

Write Code in Visual Basic Editor

Follow the steps to open the Visual Basic Editor and write some code there.

• Go to the Developer tab from the Excel ribbon.
• Click the Visual Basic option.

• In the Visual Basic for Applications window, click the Insert dropdown to select the Module.

Now that a new module is opened, write some code there and press F5 to run.

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

In this illustration, we’ll change the font color of the specified characters of a text string using the Range.Characters property in our VBA code. In cells B4:B10 we have the same text, “Change Font Color for Part of Text”. With the variation of the start and length arguments of the property, we’ll change the font color of the text.

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

We can also 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 font color for a part of text with 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 are allowed to use the ColorIndex property to set a font color easily. The following screenshot shows the color-index values in the default color palette.

Let’s 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

Read More: How to Use VBA Code to Change Font Color in Excel

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

Here 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,

• we 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.

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

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

In this example, we’ll show how to change the font color of a part of the text that is 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 that are enclosed by two brackets i.e., (Color).

To do that, let’s 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-

Read More: How to Change Font Size of the Whole Sheet with Excel VBA

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.

Conclusion

Now, we know how to change the font color for part of a text using VBA code.  Hopefully, it will help you to use these methods more confidently. Don’t forget to put any questions or suggestions in the comment box below.