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-
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
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
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.
- How to Change Font Size of the Whole Sheet with Excel VBA
- How to Add Text after Number with Custom Format in Excel (4 Ways)
- Excel VBA Codes to Bold Text in a String (5 Examples)
- How to Write 001 in Excel (11 Effective Methods)
- Text Alignment with VBA Command Button in Excel (5 Methods)
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 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-
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.
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.
- Excel VBA: Format Cell as Text (3 Methods)
- How to Format Text to Capitalize First Letter in Excel (10 Ways)
- How to Add Leading Zeros in Excel Text Format (10 Ways)
- Format Text in Excel Cell (10 Approaches)
- How to Convert Text to Time Format with AM/PM in Excel (3 Methods)
- How to Change Lowercase to Uppercase in Excel (6 Methods)