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.
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:-
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.
A new window ‘Visual Basic for Application’ will open.
Go to the ‘Insert’ tab and click on “Module” from the listed options.
Now in this VBA module, we will write our VBA code to change the font color.
To change the font color as Black to the entire dataset, use the below VBA code
To apply White color for the entire dataset range
To apply Red color for the entire dataset range
To apply Green color for the entire dataset range
To apply Blue color for the entire dataset range
To apply Yellow color for the entire dataset range
To apply Cyan color for entire dataset range
To apply Magenta color for entire dataset range
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.
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.
Color Index number = 3
Color Index number = 11
Color Index number = 21
Color Index number = 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”.
Click here ‘More Options’, you will get the below image.
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’
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.
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 value is (0, 0, 0)
RGB color value is (170, 25, 127)
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.