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, and 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:-
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.
Example 01
Color Index number = 3
Example 02
Color Index number = 11
Example 03
Color Index number = 21
Example 04
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.
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
Good Morning –
Your explanation has been great as it relates to changing the colours of the font in an active work sheet.
I have however multiple sheets in the same work book where I would love to change the font in the same cell range across all the sheets.
How can this e done across all the work books without clicking on eachand applying the macro?
Hello
First thanks for your response.
2nd in that case we need to use looping in VBA, but what you want to change font or font color in your all Excel sheets.
Thanks