3 Quick Ways to Change the Font Color in Excel VBA

This tutorial explains the three different ways to change the font color in Excel VBA.

VBA has many commands and functions that can be used. Using VBA we can easily do anything in Excel. VBA in Excel is an easy and fun thing to do.

With VBA, you can change the color of the cell, the font and even bold the characters. Using VBA for font color in Excel is possible in several ways, and it allows you to change the color of the font as well.

Here I will show you three different methods to change the font color in Excel VBA.

Below are the three different methods.

Change the Font Color – Using VBA Color property

In this method, VBA has 8 standard colors, which we can use or apply by using their names.

These colors are as follows with their VBA codes in the font color name.

Color_Name                 VBA_Code

Black              –           vbBlack

White              –           vbWhite

Red                  –           vbRed

Green               –           vbGreen

Blue                 –           vbBlue

Yellow             –           vbYellow                 

Cyan                –           vbCyan

Magenta         –           vbMagenta

We can apply these color names to change the font color in Excel VBA.

Now we will apply these color codes to change the font color with their names.

We will apply these 8 standard colors to change the font colors in the dataset below.

See the data range image below:-

Change the Font Color
Change the Font Color Image_01

To change the font color by using the VBA code, use the following procedure.

Go to the Developer tab in the ribbon area and click on it.

Then click on the “Visual Basic” option.

Change the Font Color
Change the Font Color Image_02

A new window ‘Visual Basic for Application’ will open.

Go to the ‘Insert’ tab and click on “Module” from the listed options.

Change the Font Color
Change the Font Color Image-03

Now in this VBA module, we will write our VBA code to change the font color.

 Change the Font Color
Change the Font Color Image-04

To change the font color as Black to the entire dataset, use the below VBA code

Change the Font Color
Change the Font Color Image-05

To apply White color for the entire dataset range

Change the Font Color
vbWhite

To apply Red color for the entire dataset range

Change the Font Color
vbRed

To apply Green color for the entire dataset range

vbGreen

To apply Blue color for the entire dataset range

Change the Font Color
vbBlue

To apply Yellow color for the entire dataset range

 Change the Font Color
vbYellow

To apply Cyan color for entire dataset range

Change the Font Color
vbCyan

To apply Magenta color for entire dataset range

Change the Font Color
vbMagenta

These are the 8 standard colors that we can use with their names in VBA.

Using Color Index Method

This method is different from the previous one.

In previous methods, we changed the font color by specifying the name of the color.

However, here we will change the font color with a color index or color number.

This method will allow us to change the font color by using numbers from 1 to 56.

Each number from 1 to 56 has a different color, which we can use to change the font color.

Below is an image where we have combined all the colors from 1 to 56 into one image.

Change the Font Color
All_Color Index_Number_With_Their_Color

Similarly, we have written some code below using the color index method to change the font colors.

Every image has a different color based on its color index number.

Example 01

Color Index number = 3

Change the Font Color
Color Index_3

Example 02

Color Index number = 11

Change the Font Color
Color Index_11

Example 03

Color Index number = 21

Change the Font Color
Color Index_21

Example 04

Color Index number = 51

Change the Font Color
Color Index_51

These are some examples of changing font color by the Color Index number method.

By changing the numbers starting from 1 to 56, we can change the font color.

If there are more than 56 numbers, this method will not work.

Change the Font Color Using RGB (Red, Green, Blue) Method

Previously we color the font by 8 standard colors and 56 color using color index method.

To have a wide selection of colors, though, we will need to use a function called RGB. Apart from built-in colors, we can also create our colors by using the VBA RGB function.

See below the syntax of the RGB function.

RGB (Red, Green, Blue)

Red, Green, and Blue are the three primary colors. To produce colors, we need to supply numbers from 0 to 255.

Below is a few examples for you.

To find the RGB (Red, Green, Blue) color combination in Excel, follow the below steps.

Select the font in a cell which color need to be changed.

In Excel, go to the ‘Home’ tab and then ‘Font’ group and click on the drop-down of font color “A”.

Change the Font Color
RGB_01

Click here ‘More Options’, you will get the below image.

Change the Font Color
RGB_Black

In this image, you can see that the RGB (Red, Green, Blue) value in every field is (0, 0, 0). Also, see the arrow at the bottom of the color bar.

The color is appearing in the small square box is ‘Black’

So when we put the RGB value in the VBA editor window as (0, 0, 0), it will change the font color to ‘Black’.

On the second image, you can see that the Red, Green, and Blue values are (255, 255, 255) and that the arrow is on the top of the color bar.

And see the color in the small square box at the bottom is “White”.

This means the RGB (255, 255, 255) value in the VBA editor window is ‘White’

Change the Font Color
RGB_White

Here is the third image, where we have changed the value of Red, Green, and Blue to (197, 91, 215).

The small arrow appears in the middle of the color bar and the tiny square box at the bottom displays the color of the number.

This means if you enter this number in RGB (197, 91, 215) value in the VBA editor window then you will the result as showing the color box.

Change the Font Color
RGB_New

In the same way, we can use multiple numbers in the RGB to get multiple colors.

See below for some examples to use different numbers to get a different results of colors in the RGB method.

RGB value is (127, 125, 127)

RGB_Example

RGB value is (0, 0, 0)

RGB_Example

RGB color value is (170, 25, 127)

RGB_Image

So RGB method has huge numbers of colors which we can apply in our font to change its color.

These are the three different color methods to change the font color in Excel VBA.

If you want to learn more about this, please visit Microsoft Office Support.

I hope you find this tutorial useful.

Please put your comment or suggestion in the comment box below.

Related Post

How to Change the Background Colors in Excel VBA

Copy and Paste in Excel VBA: 4 Easiest Way (For Beginners)

How to Split Cells Diagonally in Excel (Step by Step Guide)

How to Remove Formulas and Keep the Values in Excel (4 Quick Ways)

3 Easy Ways to Select the Visible Cells Only In Excel

How to Highlight Every Other Row or Column in Excel

How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel

5 Ways to Delete Blank Rows in Excel (With VBA Macro)

How to Wrap Text in Excel – 4 Easiest Method

Leave a Comment

Your email address will not be published.