VBA Font Color

Updated June 7, 2023

VBA Font Color

VBA has a lot of commands and functions to play with. We can do anything in VBA and apply that to Excel. Applying VBA in Excel is the easiest and most fun thing. VBA also has the function by which we can change the Color of cells and fonts and even bold the characters. VBA font color is used in different ways, and it helps to change the Color of the fonts in Excel.

How to Color a Font Using VBA?

Let’s see the examples of font color in Excel VBA.

Example #1 – VBA Font Color

We have sample text in an Excel sheet with the text “VBA Font Color” in cell B3 as shown below. As we can see, the font color is default black in nature.

We need a module to apply the VBA code for changing the Color of fonts for the above-shown text.

Step 1: For this, go to the VBA window and click on the Module option available in the Insert menu option, as shown below.

Step 2: Once we do that, we will get the blank window of the Module. In that, start writing a subcategory of VBA Font Color or any other name as per your need, as shown below.

Code:

Sub VBAFontColor2()

End Sub

Step 3: First, select the range of the cell where the text is located. Here, our range will be cell B3, and that will be written, followed by the “.Select” command as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select

End Sub

Step 4: As we need to change the font color, we will select the Font command with the help of Selection, as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select
Selection.Font

End Sub

Step 5: Afterward, we will select the command Color separated by a dot (.) as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select
Selection.Font.Color

End Sub

To understand any color’s formation in VBA, we have RGB, i.e., Red-Green-Blue. Numeric values of each Color range from 0 to 255. Suppose we need to change to a color font to Black; then the RGB value will be RGB(0, 0, 0). As we already have the black color font, we will try to select some other color.

Step 6: Let’s give the largest value to Green Color, the least to Red, and zero to Blue Color. As per that, consider Red at 20, Green at 230, and Blue at zero, as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select
Selection.Font.Color = RGB(20, 230, 0)

End Sub

Step 7: Now compile the code to find if it has any errors, and then run by clicking on the play button located below the menu bar. We will apply font color for the text in cell B3, which has now changed from Black to Green Color.

Example #2 – VBA Font Color

There is another way of changing the font color in VBA. For this, we will consider the same text shown in example-1, located in cell B3.

Apart from RGB, we can change the Color of fonts with the keyword “vb” followed by the name of the Color. But by this process, we can only get the main basic Color as the font color. With the help of RGB, we could get the Color of any shade just by putting the different values for Red, Green, and Blue colors.

The Colors used with vb are Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow. And how to select the Color in format is shown below.

Step 1: To apply this, go to VBA to create the subcategory in the name of VBA Font Color or any other name in a new module, as shown below.

Code:

Sub VBAFontColor3()

End Sub

Step 2: Select the range of cells we need to change the font color, as shown below.

Code:

Sub VBAFontColor3()

Range("B3").Select

End Sub

Step 3: In the same manner we have seen in example-1, use the selection function with Font and Color to activate them.

Code:

Sub VBAFontColor3()

Range("B3").Select
Selection.Font.Color

End Sub

Step 4: It lets the Color of the font from Black to Cyan. For this, select Cyan color by vbCyan, as shown below.

Code:

Sub VBAFontColor3()

Range("B3").Select
Selection.Font.Color = vbCyan

End Sub

Step 5: If required, we can compile and run the code. The font color of the text in cell B3 is changed from Black to Cyan.

Example #3 – VBA Font Color

Microsoft has defined a variety of Colors in different numbers. These are 56 in number. We can select any color code between 1 to 56 to change the font color of any cell. These color codes are shown below.

Step 1: Go to the VBA window and open a new module. In that, write the Sub Category of VBA Font Color as shown below.

Code:

Sub VBAFontColor4()

End Sub

Step 2: For this example, we will select the same text as seen in the above examples. Now select the range of the cell, which is B3, as shown below.

Code:

Sub VBAFontColor4()

Range("B3").Select

End Sub

Step 3: Select the Font function with the Selection command in the second line.

Code:

Sub VBAFontColor4()

Range("B3").Select
Selection.Font.

End Sub

Step 4: To select and apply the above-shown color code, we must select the ColorIndex function instead of Color, which we used in examples 1 and 2.

Code:

Sub VBAFontColor4()

Range("B3").Select
Selection.Font.ColorIndex = 46

End Sub

And at last, select the color code we need to see in a selected range of cells. Let’s select color code 46 which is used for the Orange color.

Step 5: Now run the code to see the change. The color font in cell B3 is now changed from black to orange.

Pros of VBA Font Color

• It is easy to implement.
• With the help of RGB, we can change the color of any shade we want.
• It helps create the dashboard where we need to show the different types of data in a different colors with the help of VBA.

Things to Remember

• It is always recommended to use RGB when we do not know the color code. By giving a different color range from 0 to 255 in RGB, we can create any color from dark to a bright shade of our choice.
• Saving the file in Macro Enable Excel helps to use and visit the written code multiple times.
• Although changing font color in Excel is the easiest way to do, automating this activity in huge work can save time and avoid the chances when the file may crash or hang.

Recommended Articles

This is a guide to VBA Font Color. Here we discuss how to use Excel VBA Font Color, a few practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

1. VBA While Loop
2. VBA Remove Duplicates
3. Excel VBA MsgBox
4. VBA Sleep