My Excel Journey

Changing the font color with VBA in Excel is simple, powerful, and fun. VBA offers many commands and functions that let you automate formatting, apply colors, and customize how your data looks.

In this tutorial, I’ll show you three different methods to change font color in Excel VBA:

Using VBA Color Property

Using ColorIndex

Using RGB Function

Change Font Color Using VBA Color Property

VBA provides 8 standard color constants that can be directly used by their names:

Color Name	VBA Code
Black	`vbBlack`
White	`vbWhite`
Red	`vbRed`
Green	`vbGreen`
Blue	`vbBlue`
Yellow	`vbYellow`

Cyan vbCyan

Magenta vbMagenta

👉 Example: To change font color to Red for a range:

Range("A1:A10").Font.Color = vbRed

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 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 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 Image-03

Now in this VBA module, we will write our VBA code to 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 Image-05

To apply White color for the entire dataset range

vbWhite

To apply Red color for the entire dataset range

vbRed

To apply Green color for the entire dataset range

vbGreen

To apply Blue color for the entire dataset range

vbBlue

To apply Yellow color for the entire dataset range

vbYellow

To apply Cyan color for entire dataset range

vbCyan

To apply Magenta color for entire dataset range

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.

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

Color Index_3

Example 02

Color Index number = 11

Color Index_11

Example 03

Color Index number = 21

Color Index_21

Example 04

Color Index number = 51

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”.

RGB_01

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

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’

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.

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 will find this topic useful. please feel free to put your comments or suggestion.

Thanks

Narendra Singh

Creating a triangle pattern using VBA in Excel can be a fun and educational exercise. Below is a simple guide on how to do it, along with the VBA code required to create a triangle pattern with the * sign in an Excel worksheet.

See in the image, we want to create this kind of Triangle in Excel with the help of VBA.

Use the following steps to go there.

Step 1: Open Excel and Access the VBA Editor

1. Open Excel.

2. Press Alt + F11 to open the VBA Editor.

3. In the VBA Editor, insert a new module by clicking Insert > Module

Step 2: Write the VBA Code

Use the following VBA code into the module:

Sub CreateTriangle()

Dim ws As Worksheet

Dim i As Integer, j As Integer

Dim rowCount As Integer

' Set the worksheet where the triangle will be created

Set ws = ThisWorkbook.Sheets("Sheet1")

' Clear the worksheet

ws.Cells.Clear

' Define the number of rows for the triangle

rowCount = 10

' Loop through rows and columns to create the triangle pattern

For i = 1 To rowCount

For j = 1 To i

ws.Cells(i, j).Value = "*"

Next j

Next i

ws.Columns.AutoFit

End Sub

Copy this entire VBA code and paste into your Excel worksheet.

Press F5 to run the code.

Step 4: View the Triangle Pattern

Go back to your Excel worksheet (usually named "Sheet1"). You should now see a triangle pattern made of * signs, starting from cell A1.

Explanation of the Code

Set ws: Defines the worksheet where the triangle will be created.

ws.Cells.Clear: Clears any existing content on the worksheet to start fresh.

rowCount = 10: Sets the number of rows for the triangle. You can change this value to create a l arger or smaller triangle.

Nested For Loops: The outer loop runs through each row, and the inner loop places the * sign in the appropriate columns to form a triangle.

ws.Columns.AutoFit: Automatically adjusts the width of all columns to fit the content.

Note:- To change the size of the triangle, modify the rowCount variable.

If you want to use a different character instead of *, replace the * in the line ws.Cells(i, j).Value = "*" with your desired character.

With these steps, you can easily create a triangle pattern in Excel using VBA and ensure the columns are autofit to display the pattern neatly. Feel free to experiment with different shapes and patterns to enhance your VBA skills!