How To Use VBA Code To Change Font Color In Excel (3 Methods)

Dataset Overview

Suppose we have a dataset containing information about several sales representatives from the Armani group. The dataset includes columns for the sales representatives’ names, identification numbers, product types, and revenue earned.

Method 1 – Using Color Constants in VBA Code

There are eight types of color constants that you can use in your VBA code. They are:

1. vbRed: Red
2. vbGreen: Green
3. vbMagenta: Magenta
4. vbBlack: Black
5. vbBlue: Blue
6. vbWhite: White
7. vbYellow: Yellow
8. vbCyan: Cyan

Follow these steps to change font color using color constants in VBA:

• Open a Module:
  • Go to the Developer tab.

• • Click on Visual Basic to open the Microsoft Visual Basic for Applications window.
  • Insert a new module by going to Insert → Module.

• Write the VBA Code:
  • In the newly created module, enter the following VBA code:

Sub VBA_to_Change_Font_Color() Range("B5:B14").Font.Color = vbGreen Range("C5:C14").Font.Color = vbRed Range("D5:D14").Font.Color = vbGreen Range("E5:E14").Font.Color = vbMagenta End Sub

• Run the VBA Code:
  • Click Run → Run Sub/UserForm to execute the VBA code.

• • The font color will change according to the specified criteria.

Read More: Excel VBA: Change Font Color for Part of Text

Method 2 – Applying Color Index Command

The Color Index command assigns distinct numbers to various colors in Excel. There are 56 color codes available. To modify the font color of any cell, you can choose a color code from 1 to 56. Here’s how to change font color using the Color Index:

• Open a Module:
  • Insert a new module following the steps from Method 1.
  • Enter the following VBA code to change the font color from black to red (using color index 3):

Sub VBA_to_Change_Font_Color() Range("C5:C14").Select Selection.Font.ColorIndex = 3 End Sub

• Run the VBA Code:
  • Click Run → Run Sub/UserForm to execute the VBA code.

• • The font color will change according to the specified criteria.

Method 3 – Inserting RGB Color Code

You can also use the RGB color code to customize font colors using VBA in Excel. RGB stands for red, green, and blue, and it allows you to create custom colors. Follow these steps:

• Insert a New Module:
  • Add a new module as described in Method 1.
  • Insert the following VBA code to change the font color of column D to a custom color (RGB value 250, 125, 250):

Sub Change_Color_Font() Range("D5:D14").Font.Color = RGB(250, 125, 250) End Sub

• Run the VBA Code:
  • Click Run → Run Sub/UserForm to apply the custom font color.

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

Things to Remember

• You can open the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
• If the Developer tab is not visible in your ribbon, enable it by going to File → Options → Customize Ribbon.

Download Practice Workbook

You can download the practice workbook from here:

Change Font Color.xlsm Get FREE Advanced Excel Exercises with Solutions!