Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Change Font Color for Part of Text in 8 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VBA Change Font Color for Part of Text in 8 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)In this Excel VBA Change Font Color for Part of Text Tutorial, you learn how to change a font's color:

  • With Excel macros.
  • For part of text (inside a cell).

This Excel VBA Change Font Color for Part of Text Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Change Font Color for Part of Text Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section below.

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

This Excel VBA Change Font Color for Part of Text Tutorial is part of a more comprehensive series of Excel VBA Font Color Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

The VBA Change Font Color for Part of Text Snippet Template/Structure

The following is the VBA change font color for part of text snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/

Dim MyCellObjectVariable As Range
Dim MyTextToChangeColor As String
Dim iCounter As Long
     
Set MyCellObjectVariable = WorkbookObjectReference.WorksheetObjectReference.CellRangeObjectReference
    
MyTextToChangeColor = "StringWithTextToChangeColor"

For iCounter = 1 To Len(MyCellObjectVariable.Value)
    If Mid(MyCellObjectVariable.Value, iCounter, Len(MyTextToChangeColor)) = MyTextToChangeColor Then
        MyCellObjectVariable.Characters(iCounter, Len(MyTextToChangeColor)).Font.ColorOrColorIndex = NewColorOrColorIndexValue
    End If
Next iCounter


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

The Example Before VBA Change Font Color for Part of Text

This Excel VBA Change Font Color for Part of Text Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Change Font Color for Part of Text Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section above.

The text in cell A6 of the example worksheet is “Change font color of all appearances of text in this text, with an Excel VBA macro”.

This is the cell the VBA change font color part text example macro I create (by following the step-by-step process below) works with. In other words: This is the cell where the example macro (when executed) changes the font color for part of the text.

The following image displays the example worksheet before I execute the VBA change font color part text example macro.

Example Excel workbook before VBA change font color part text example macro


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 1: Create Object Variable Representing Cell (Where You Change the Font Color for Part of the Text)

  1. Refer to the cell where you change the font color for part of the text; and
  2. Assign the object reference to an object variable.

In other words:

  1. Declare an object variable to represent the cell containing the text you work with (where you change the font color for part of the text); and
  2. Assign (to that object variable) a reference to a Range object representing that cell.

Consider explicitly declaring the object variable representing the cell (where you change the font color for part of the text). As a general rule, declare this object variable:

  • Using the Dim statement; and
  • As of the Range object data type.

If the scope of the object variable is module-level or global, follow the applicable rules for (module-level or global) variable declaration.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyCellObjectVariable As Range

Use the Set statement to assign an object reference to the applicable object variable.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyCellObjectVariable As Range
Set MyCellObjectVariable =

Consider explicitly including the following references to create a fully qualified object reference returning a Range object representing a cell:

  1. A reference to the applicable workbook. The following VBA constructs (among others) may return a Workbook object:
    • The Application.ThisWorkbook property.
    • The Application.Workbooks and Workbooks.Item properties.
    • The Application.ActiveWorkbook property.
  2. A reference to the applicable worksheet. The following VBA constructs (among others) may return a Worksheet object:
    • The Workbook.Sheets and Sheets.Item properties.
    • The Workbook.Worksheets and Worksheets.Item properties.
    • The Application.ActiveSheet property.
  3. A reference to the applicable cell. The following VBA constructs (among others) may return a Range object representing a cell:
    • The Worksheet.Range property.
    • The Worksheet.Cells and Range.Item properties.
    • The Range.Range property.
    • The Range.Cells and Range.Item properties.
    • The Range.Offset property.
    • The Range.End property.
    • The Range.Resize property.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyCellObjectVariable As Range
Set MyCellObjectVariable = WorkbookObjectReference.WorksheetObjectReference.CellRangeObjectReference


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 1 Example

In the example, I:

  1. Declare an object variable representing the cell:
    1. As of the Range object data type; and
    2. With the name “MyCell”
  2. Refer (and set the object variable) to cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored. I work with the following VBA constructs to obtain a Range object representing this cell:
    1. The Application.ThisWorkbook property: ThisWorkbook.
    2. The Workbook.Worksheets and Worksheets.Item properties: Worksheets(“Excel VBA Font Color Part Text”).
    3. The Worksheet.Range property: Range(“A6”).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyCell As Range
Set MyCell = ThisWorkbook.Worksheets("Excel VBA Font Color Part Text").Range("A6")


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 2: Loop Through Each Character in the Full String (with the Substring Whose Font Color You Change)

Work with the For… Next loop to iterate through each character in the text (the full string) stored inside the cell you work with (identified in step #1).

To iterate through each character in the text stored inside the cell you work with, specify the main elements of the For… Next loop as follows:

  • Counter: An iteration variable of (usually) the Long data type.
  • Start: 1.
  • End: The number of characters in the string stored inside the cell you work with, as returned by the Len function.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
For iCounter = 1 To Len(StringArgument)
    StatementsInsideLoop
Next iCounter

Consider explicitly declaring the iteration variable. As a general rule, declare this object variable:

  • Using the Dim statement; and
  • As of the Long data type.

If the scope of the iteration variable is module-level or global, follow the applicable rules for (module-level or global) variable declaration.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim iCounter As Long
For iCounter = 1 To Len(StringArgument)
    StatementsInsideLoop
Next iCounter

Set the String argument of the Len function to the text (the full string) stored inside the cell you work with (identified in step #1), as returned by the Range.Value property.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim iCounter As Long
For iCounter = 1 To Len(MyCellObjectVariable.Value)
    StatementsInsideLoop
Next iCounter


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 2 Example

In the example, I do the following:

(1) Declare 2 variables:

  1. A variable representing the full text stored inside cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored:
    • As of the String data type; and
    • With the name “MyFullText”.
  2. The iteration variable:
    • As of the Long data type; and
    • With the name “iCounter”.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyFullText As String
Dim iCounter As Long

(2) Work with the Range.Value property to obtain the text (the full string) stored inside the cell represented by the MyCell object variable (declared in step #1), and assign this string to the MyFullText variable (declared in this step #2).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyFullText As String
Dim iCounter As Long
MyFullText = MyCell.Value

(3) Set the String argument of the Len function (used to specify End inside the opening statement of the For… Next loop) to the string represented by the MyFullText variable.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyFullText As String
Dim iCounter As Long
MyFullText = MyCell.Value
For iCounter = 1 To Len(MyFullText)
    '...
Next iCounter


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 3: Specify the Part of Text (Substring) Whose Font Color You Change

Specify the part of text (the substring) whose font color you change.

Consider explicitly declaring a variable to represent the string (with the part of text whose font color you change). As a general rule, declare this variable:

  • Using the Dim statement; and
  • As of the String data type.

If the scope of the variable is module-level or global, follow the applicable rules for (module-level or global) variable declaration.

When specifying the string with the part of text (the substring) whose font color you change, consider whether you must wrap this string in double quotes (“StringWithTextToChangeColor”).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyTextToChangeColor As String
MyTextToChangeColor = "StringWithTextToChangeColor"


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 3 Example

The VBA change font color part text example macro changes the font color of all occurrences of the word “text”. In other words: “text” is the part of text (the substring) whose font color the example macro changes.

The “text” string is represented by a variable:

  • Of the String data type; and
  • With the name “MyTextToChangeColor”.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyTextToChangeColor As String
MyTextToChangeColor = "text"


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 4: Extract a Helper Substring from the Full String (with the Substring Whose Font Color You Change) in Each Loop Iteration

Inside the loop (created in step #2), work with the Mid function to extract a helper substring (from the full string stored inside the cell you specified in step #1) with the following characteristics:

  • Starting at the character whose position is equal to the current loop iteration. In other words:
    • First loop iteration: String starting at the first position of the full string stored in the cell.
    • Second loop iteration: String starting at the second position of the full string stored in the cell.
    • Third loop iteration: String starting at the third position of the full string stored in the cell.
    • …
    • Nth loop iteration: String starting at the nth position of the full string stored in the cell.
  • Length equal to the length of the part of text (the substring) whose font color you change (you specified in step #3).

To extract the helper substring, specify Mid's arguments as follows:

  • String: The text (the full string) stored inside the cell you work with (identified in step #1), as returned by the Range.Value property.
  • Start: The loop iteration variable (declared in step #2).
  • Length: The number of characters in the part of text (the substring) whose font color you change (you specified in step #3).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Mid(MyCellObjectVariable.Value, iCounter, NumberOfCharactersInStringWithTextToChangeColor)

Consider:

  • Working with the Len function to count the number of characters in the part of text (the substring) whose font color you change; and
  • Setting the Length argument of the Mid function to the value returned by the Len function.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Mid(MyCellObjectVariable.Value, iCounter, Len("StringWithTextToChangeColor"))

If (as I suggest in step #3), you explicitly declare the variable representing the part of text (the substring) whose font color you change:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Mid(MyCellObjectVariable.Value, iCounter, Len(MyTextToChangeColor))


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 4 Example

In the example, I:

  1. Declare a variable representing the number of characters in the part of text (the substring) whose font color the VBA change font color part text example macro changes:
    • As of the Long data type; and
    • With the name “MyTextToChangeColorLength”.
  2. Use the Len function to count the number of characters in the part of text (the substring) whose font color the example macro changes (represented by the MyTextToChangeColor variable declared in step #3).
  3. Assign the value returned by the Len function to the MyTextToChangeColorLength variable.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyTextToChangeColorLength As Long
MyTextToChangeColorLength = Len(MyTextToChangeColor)

Therefore, in the Mid function:

  • The MyFullText variable (declared in step #2) represents the full text stored inside cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored.
  • The iCounter variable (declared in step #2) is the loop iteration variable.
  • The MyTextToChangeColorLength variable (declared in this step #4) represents the number of characters in the part of text (the substring) whose font color the VBA change font color part text example macro changes.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Dim MyTextToChangeColorLength As Long
MyTextToChangeColorLength = Len(MyTextToChangeColor)
Mid(MyFullText, iCounter, MyTextToChangeColorLength)


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 5: Test Whether the Helper Substring is Equal to the Part of Text (Substring) Whose Font Color You Change

Inside the loop (created in step #2), use the equal to operator (=) to compare 2 strings:

  • The helper string extracted in step #4; and
  • The part of text (the substring) whose font color you change (you specified in step #3).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Mid(MyCellObjectVariable.Value, iCounter, Len(MyTextToChangeColor)) = MyTextToChangeColor


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 5 Example

  • The MyFullText variable (declared in step #2) represents the full text stored inside cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored.
  • The iCounter variable (declared in step #2) is the loop iteration variable.
  • The MyTextToChangeColorLength variable (declared in step #4) represents the number of characters in the part of text (the substring) whose font color the VBA change font color part text example macro changes.
  • The MyTextToChangeColor variable (declared in step #3) represents the part of text (the substring) whose font color the example macro changes.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
Mid(MyFullText, iCounter, MyTextToChangeColorLength) = MyTextToChangeColor


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 6: Instruct Excel to Execute the Statement (Changing the Font Color for Part of the Text) if the Helper Substring is Equal to the Part of Text (Substring) Whose Font Color You Change

Work with the If… Then… Else statement (inside the For… Next loop created in step #2) to conditionally execute a statement.

  • The statement to execute is that changing the font color for part of the text. You build this statement in steps #7 and #8.
  • The conditional test (on which statement execution depends) tests whether the helper string (extracted in step #4) is equal to the part of text (the substring) whose font color you change (you specified in step #3). You built this conditional test in step #5.

Depending on the case you deal with, you may need to work with different versions of the If… Then… Else statement. Consider the following 4 basic versions of the If… Then… Else statement:

  • If… Then. This is the version I use in this Excel VBA Change Font Color for Part of Text Tutorial.
  • If… Then… Else.
  • If… Then… ElseIf.
  • If… Then… ElseIf… Else.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
If Mid(MyCellObjectVariable.Value, iCounter, Len(MyTextToChangeColor)) = MyTextToChangeColor Then
    StatementThatChangesFontColorForPartOfText
End If


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 6 Example

  • The MyFullText variable (declared in step #2) represents the full text stored inside cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored.
  • The iCounter variable (declared in step #2) is the loop iteration variable.
  • The MyTextToChangeColorLength variable (declared in step #4) represents the number of characters in the part of text (the substring) whose font color the VBA change font color part text example macro changes.
  • The MyTextToChangeColor variable (declared in step #3) represents the part of text (the substring) whose font color the example macro changes.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
If Mid(MyFullText, iCounter, MyTextToChangeColorLength) = MyTextToChangeColor Then
    '...
End If


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 7: Identify Part of Text Whose Font Color You Change

Work with the Range.Characters property to return a Characters object representing a range of characters (the part of the text whose font color you change).

To identify the part of text whose font color you change, make the Range.Characters property work with the object variable representing the cell where you change the font color for part of the text (declared in step #1).

To return the part of text whose font color you change, specify Range.Characters' parameters as follows:

  • Start: The loop iteration variable (declared in step #2).
  • Length: The number of characters in the part of text (the substring) whose font color you change (counted in step #4).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
MyCellObjectVariable.Characters(iCounter, Len(MyTextToChangeColor))


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 7 Example

  • The MyCell object variable (declared in step #1) represents cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored.
  • The iCounter variable (declared in step #2) is the loop iteration variable.
  • The MyTextToChangeColorLength variable (declared in step #4) represents the number of characters in the part of text (the substring) whose font color the VBA change font color part text example macro changes.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
MyCell.Characters(iCounter, MyTextToChangeColorLength)


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 8: Set the Value of the Font.Color or Font.ColorIndex Property

Use either of the following 2 properties to set the color of the applicable font:

  • Font.Color.
  • Font.ColorIndex.

Do the following to set the value of the Font.Color or Font.ColorIndex property:

(1) Start with the Characters object reference you created in step #7.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
MyCellObjectVariable.Characters(iCounter, Len(MyTextToChangeColor))

(2) Refer to the following:

  • The Characters.Font property; and
  • The Font.Color property or the Font.ColorIndex property (as applicable).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
MyCellObjectVariable.Characters(iCounter, Len(MyTextToChangeColor)).Font.ColorOrColorIndex

(3) Set the value of the Font.Color property or Font.ColorIndex property.

If you don't know how to work with the Font.Color or Font.ColorIndex properties, I suggest you read the applicable Tutorials I link to in the Related Excel Macro and VBA Training Materials and Resources Section above.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
MyCellObjectVariable.Characters(iCounter, Len(MyTextToChangeColor)).Font.ColorOrColorIndex = NewColorOrColorIndexValue


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Step 8 Example

  • The MyCell object variable (declared in step #1) represents cell A6 in the “Excel VBA Font Color Part Text” worksheet in the workbook where the procedure is stored.
  • The iCounter variable (declared in step #2) is the loop iteration variable.
  • The MyTextToChangeColorLength variable (declared in step #4) represents the number of characters in the part of text (the substring) whose font color the VBA change font color part text example macro changes.

I use the RGB function to set the value of the Font.Color property (Font.Color = RGB(0, 128, 55)). I specify the Red, Green, and Blue arguments of the RGB function as follows:

  • Red: 0.
  • Green: 128.
  • Blue: 55.

If you don't know how to work with the RGB function to set the Font.Color property, I suggest you read my Excel VBA Font Color RGB Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section above.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-part/
MyCell.Characters(iCounter, MyTextToChangeColorLength).Font.Color = RGB(0, 128, 55)

I organize the full VBA change font color part text example macro as follows:

(1) Start with the variable declaration statements created in the following steps:

  • Step #1: MyCell object variable.
  • Step #2:
    • MyFullText variable.
    • iCounter variable.
  • Step #3: MyTextToChangeColor variable.
  • Step #4: MyTextToChangeColorLength variable.
Sub FontColorPartOfText()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-font-color-part/
    
    'Declare the following:
        'Step 1: Object variable to represent cell where you change the font color for part of the text
        'Variable to represent:
            'Step 2: Full text (with the substring whose font color you change)
            'Step 3: The text portion (substring) whose font color you change
            'Step 4: Number of characters in text portion (substring) whose font color you change
        'Step 2: Loop iteration variable
    Dim MyCell As Range
    Dim MyFullText As String
    Dim MyTextToChangeColor As String
    Dim MyTextToChangeColorLength As Long
    Dim iCounter As Long

End Sub

(2) Add the assignment statements created in the following steps:

  • Step #1: Assign object reference to MyCell object variable.
  • Step #2: Assign string to MyFullText variable.
  • Step #4:
    • Assign string to MyTextToChangeColor variable.
    • Assign value to MyTextToChangeColorLength variable.
Sub FontColorPartOfText()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-font-color-part/
    
    'Declare the following:
        'Step 1: Object variable to represent cell where you change the font color for part of the text
        'Variable to represent:
            'Step 2: Full text (with the substring whose font color you change)
            'Step 3: The text portion (substring) whose font color you change
            'Step 4: Number of characters in text portion (substring) whose font color you change
        'Step 2: Loop iteration variable
    Dim MyCell As Range
    Dim MyFullText As String
    Dim MyTextToChangeColor As String
    Dim MyTextToChangeColorLength As Long
    Dim iCounter As Long
    
    'Step 1: Assign object reference (to cell A6 in the "Excel VBA Font Color Part Text" worksheet in this workbook) to MyCell object variable
    Set MyCell = ThisWorkbook.Worksheets("Excel VBA Font Color Part Text").Range("A6")
    
    'Step 2: Assign string stored in cell represented by MyCell object variable to MyFullText variable
    MyFullText = MyCell.Value
    
    'Step 4: Assign string with text whose color you change to MyTextToChangeColor variable
    MyTextToChangeColor = "text"
    
    'Step 4: Assign number of characters in string represented by MyTextToChangeColor variable to MyTextToChangeColorLength variable
    MyTextToChangeColorLength = Len(MyTextToChangeColor)
    
End Sub

(3) Add the loop created in step #2.

Sub FontColorPartOfText()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-font-color-part/
    
    'Declare the following:
        'Step 1: Object variable to represent cell where you change the font color for part of the text
        'Variable to represent:
            'Step 2: Full text (with the substring whose font color you change)
            'Step 3: The text portion (substring) whose font color you change
            'Step 4: Number of characters in text portion (substring) whose font color you change
        'Step 2: Loop iteration variable
    Dim MyCell As Range
    Dim MyFullText As String
    Dim MyTextToChangeColor As String
    Dim MyTextToChangeColorLength As Long
    Dim iCounter As Long
    
    'Step 1: Assign object reference (to cell A6 in the "Excel VBA Font Color Part Text" worksheet in this workbook) to MyCell object variable
    Set MyCell = ThisWorkbook.Worksheets("Excel VBA Font Color Part Text").Range("A6")
    
    'Step 2: Assign string stored in cell represented by MyCell object variable to MyFullText variable
    MyFullText = MyCell.Value
    
    'Step 4: Assign string with text whose color you change to MyTextToChangeColor variable
    MyTextToChangeColor = "text"
    
    'Step 4: Assign number of characters in string represented by MyTextToChangeColor variable to MyTextToChangeColorLength variable
    MyTextToChangeColorLength = Len(MyTextToChangeColor)
    
    'Step 2: Loop from 1 to the number of characters in the string represented by MyFullText variable
    For iCounter = 1 To Len(MyFullText)
        '...
    Next iCounter

End Sub

(4) Nest (inside the loop) the If… Then block created in steps #4, #5, and #6.

Sub FontColorPartOfText()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-font-color-part/
    
    'Declare the following:
        'Step 1: Object variable to represent cell where you change the font color for part of the text
        'Variable to represent:
            'Step 2: Full text (with the substring whose font color you change)
            'Step 3: The text portion (substring) whose font color you change
            'Step 4: Number of characters in text portion (substring) whose font color you change
        'Step 2: Loop iteration variable
    Dim MyCell As Range
    Dim MyFullText As String
    Dim MyTextToChangeColor As String
    Dim MyTextToChangeColorLength As Long
    Dim iCounter As Long
    
    'Step 1: Assign object reference (to cell A6 in the "Excel VBA Font Color Part Text" worksheet in this workbook) to MyCell object variable
    Set MyCell = ThisWorkbook.Worksheets("Excel VBA Font Color Part Text").Range("A6")
    
    'Step 2: Assign string stored in cell represented by MyCell object variable to MyFullText variable
    MyFullText = MyCell.Value
    
    'Step 4: Assign string with text whose color you change to MyTextToChangeColor variable
    MyTextToChangeColor = "text"
    
    'Step 4: Assign number of characters in string represented by MyTextToChangeColor variable to MyTextToChangeColorLength variable
    MyTextToChangeColorLength = Len(MyTextToChangeColor)
    
    'Step 2: Loop from 1 to the number of characters in the string represented by MyFullText variable
    For iCounter = 1 To Len(MyFullText)
        
        'Do the following:
            'Step 4: Extract a helper string from the string represented by the MyFullText variable
                'Starting at the position equal to the current loop iteration
                'With a length equal to the number represented by the MyTextToChangeColorLength variable
            'Step 5: Test whether the helper string is equal to the string represented by the MyTextToChangeColor variable
            'Step 6: If the helper string is equal to the string represented by the MyTextToChangeColor variable, change the text portion (substring) whose font color you change
        If Mid(MyFullText, iCounter, MyTextToChangeColorLength) = MyTextToChangeColor Then
        
            '...

        End If
    
    Next iCounter

End Sub

(4) Nest (inside the If… Then block) the statement that changes the font color for part of the text, created in steps #7 and #8. The result is the full VBA change font color part text example macro.

Sub FontColorPartOfText()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-font-color-part/
    
    'Declare the following:
        'Step 1: Object variable to represent cell where you change the font color for part of the text
        'Variable to represent:
            'Step 2: Full text (with the substring whose font color you change)
            'Step 3: The text portion (substring) whose font color you change
            'Step 4: Number of characters in text portion (substring) whose font color you change
        'Step 2: Loop iteration variable
    Dim MyCell As Range
    Dim MyFullText As String
    Dim MyTextToChangeColor As String
    Dim MyTextToChangeColorLength As Long
    Dim iCounter As Long
    
    'Step 1: Assign object reference (to cell A6 in the "Excel VBA Font Color Part Text" worksheet in this workbook) to MyCell object variable
    Set MyCell = ThisWorkbook.Worksheets("Excel VBA Font Color Part Text").Range("A6")
    
    'Step 2: Assign string stored in cell represented by MyCell object variable to MyFullText variable
    MyFullText = MyCell.Value
    
    'Step 4: Assign string with text whose color you change to MyTextToChangeColor variable
    MyTextToChangeColor = "text"
    
    'Step 4: Assign number of characters in string represented by MyTextToChangeColor variable to MyTextToChangeColorLength variable
    MyTextToChangeColorLength = Len(MyTextToChangeColor)
    
    'Step 2: Loop from 1 to the number of characters in the string represented by MyFullText variable
    For iCounter = 1 To Len(MyFullText)
        
        'Do the following:
            'Step 4: Extract a helper string from the string represented by the MyFullText variable
                'Starting at the position equal to the current loop iteration
                'With a length equal to the number represented by the MyTextToChangeColorLength variable
            'Step 5: Test whether the helper string is equal to the string represented by the MyTextToChangeColor variable
            'Step 6: If the helper string is equal to the string represented by the MyTextToChangeColor variable, change the text portion (substring) whose font color you change
        If Mid(MyFullText, iCounter, MyTextToChangeColorLength) = MyTextToChangeColor Then
            
            'Step 7: Identify the part of text whose font color you change
            'Step 8: Use the RGB function to set the value of the Font.Color property. Specify the red, green, and blue components of the color as follows:
                'Red: 0
                'Green: 128
                'Blue: 55
            MyCell.Characters(iCounter, MyTextToChangeColorLength).Font.Color = RGB(0, 128, 55)
        
        End If
    
    Next iCounter

End Sub

The following GIF illustrates the effects of using the VBA change font color part text example macro.

Example: Set Excel VBA Font Color for Part of Text with macros


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

Download the VBA Change Font Color for Part of Text Example Workbook

This Excel VBA Change Font Color for Part of Text Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Change Font Color for Part of Text workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Change Font Color for Part of Text Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section above.

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

This Excel VBA Change Font Color for Part of Text Tutorial is part of a more comprehensive series of Excel VBA Font Color Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

Subscribe
Notify of
guest
2 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure