VBA Font Color

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 a fun thing. VBA also has the function by which we can change the color of cell, fonts and we can even bold the characters as well. 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 color of the font is default black color in nature.

To apply the VBA code for changing the color of fonts for above-shown text, we need a module.

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

Step 2: Once we do that, we will get the blank window of Module. In that, start writing subcategory of VBA Font Color or in 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 “.Select” command as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select

End Sub

Step 4: As we need to change the color of fonts so 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: After that, we will select the command Color separated by a dot (.) as shown below.

Code:

Sub VBAFontColor2()

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

End Sub

Now to understand the formation of any color, 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 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 and least to Red and zero to Blue color. As per that, considering 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 error and then run by clicking on the play button located below the menu bar. We will apply font color for text which is in cell B3 and 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 as 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. Where with the help of RGB we could get color of any shade, just by putting the different values for Red, Green and Blue colors.

The color which can be 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 in any other name in a new module as shown below.

Code:

Sub VBAFontColor3()

End Sub

Step 2: Select the range of cell for which we need to change the font color as shown below.

Code:

Sub VBAFontColor3()

Range("B3").Select

End Sub

Step 3: In the same manner what we have seen in example-1, use 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 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 then we can compile the code and then run it. We will see the font color of text at cell B3 is changed from Black to Cyan.

Example #3 – VBA Font Color

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

Step 1: Now 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: Now in the second line, select the Font function with Selection command.

Code:

Sub VBAFontColor4()

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

End Sub

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

Code:

Sub VBAFontColor4()

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

End Sub

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

Step 5: Now run the code to see the change. We will the color font at 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 in creating the dashboard where we need to show the different types of data in a different color 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 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, but automating this activity in huge set of work can save the time and it will avoid the chances when the file may get crash or hang.

Recommended Articles

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

1. VBA While Loop
2. VBA Remove Duplicates
3. VBA Data Types
4. VBA Sleep