Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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

In this section, we will demonstrate 3 effective examples to change 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 New 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 a 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

Excel VBA Change Font Color for Part of Text

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

Excel VBA Change Font Color for Part of Text


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

Excel VBA Change Font Color for Part of Text


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 colorindex 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

Excel VBA Change Font Color for Part of Text

Read More: How to Change Case in Excel without a Formula (5 Ways)


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.

Excel VBA Change Font Color for Part of Text

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.

Excel VBA Change Font Color for Part of Text

Read More: How to Use VBA Code to Change Font Color in Excel (3 Methods)


Similar Readings


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 “Colorsmultiple times in it. We want to change the font of only those words that are enclosed by two brackets i.e., (Color).

Excel VBA Change Font Color for Part of Text

To do that, let’s run the code in the visual code 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-

Excel VBA Change Font Color for Part of Text

Read More: [Fixed!] Unable to Change Font Color in Excel (3 Solutions)


Things to Remember

  • While writing a 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.

Conclusion

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


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

6 Comments
  1. Simple to follow, knowledgeable, THANK YOU, sir.

    • You are welcomed Jaisinh Chavan. Thanks for your kind words.

      Regards
      Maruf Islam (Exceldemy Team)

  2. Hello

    I did and implemented third Method to Change Font Color for Part of Text Using VBA in Excel. It worked very well for texts in English fonts, but it didn’t worked for texts in Persian fonts. Do you have any solution for solving this problem?

    Thank you and with best regards.

    • Greetings Ramin Janani,

      strCharacter = "("
      endCharacter = ")"

      This part of the macro takes the 1st brackets as characters to trigger the macro to Color Text or Characters within them. Make sure you encompass the text or characters with brackets. I’ve tested the macro in languages other than English and it works.
      Of course, you can change the strCharacter and endCharacter with whatever character (i.e., (),{},[]) you assign to in the macro.

      Regards,
      Md. Maruf Islam (Exceldemy Team)

  3. This is an excellent answer.

Leave a reply

ExcelDemy
Logo