How to Use VBA Code to Change Font Color in Excel (3 Methods)

Dataset Overview

Suppose we have a dataset containing information about several sales representatives from the Armani group. The dataset includes columns for the sales representatives’ names, identification numbers, product types, and revenue earned.

vba code to change font color

Method 1 – Using Color Constants in VBA Code

There are eight types of color constants that you can use in your VBA code. They are:

vbRed: Red
vbGreen: Green
vbMagenta: Magenta
vbBlack: Black
vbBlue: Blue
vbWhite: White
vbYellow: Yellow
vbCyan: Cyan

Follow these steps to change font color using color constants in VBA:

Open a Module:
- Go to the Developer tab.

- Click on Visual Basic to open the Microsoft Visual Basic for Applications window.
- Insert a new module by going to Insert → Module.

Write the VBA Code:
- In the newly created module, enter the following VBA code:

Sub VBA_to_Change_Font_Color()
Range("B5:B14").Font.Color = vbGreen
Range("C5:C14").Font.Color = vbRed
Range("D5:D14").Font.Color = vbGreen
Range("E5:E14").Font.Color = vbMagenta
End Sub

Run the VBA Code:
- Click Run → Run Sub/UserForm to execute the VBA code.

- The font color will change according to the specified criteria.

Method 2 – Applying Color Index Command

The Color Index command assigns distinct numbers to various colors in Excel. There are 56 color codes available. To modify the font color of any cell, you can choose a color code from 1 to 56. Here’s how to change font color using the Color Index:

Open a Module:
- Insert a new module following the steps from Method 1.
- Enter the following VBA code to change the font color from black to red (using color index 3):

Sub VBA_to_Change_Font_Color()
Range("C5:C14").Select
Selection.Font.ColorIndex = 3
End Sub

Run the VBA Code:
- Click Run → Run Sub/UserForm to execute the VBA code.

- The font color will change according to the specified criteria.

Method 3 – Inserting RGB Color Code

You can also use the RGB color code to customize font colors using VBA in Excel. RGB stands for red, green, and blue, and it allows you to create custom colors. Follow these steps:

Insert a New Module:
- Add a new module as described in Method 1.
- Insert the following VBA code to change the font color of column D to a custom color (RGB value 250, 125, 250):

Sub Change_Color_Font()
Range("D5:D14").Font.Color = RGB(250, 125, 250)
End Sub

Run the VBA Code:
- Click Run → Run Sub/UserForm to apply the custom font color.

Things to Remember

You can open the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
If the Developer tab is not visible in your ribbon, enable it by going to File → Options → Customize Ribbon.

Download Practice Workbook

You can download the practice workbook from here:

Change Font Color.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Sub Change_Font_Across_All_Sheets() Dim wb As Workbook Dim ws As Worksheet Dim cellRange As Range Dim fontName As String Dim fontSize As Integer ' Set the workbook object Set wb = ThisWorkbook ' Specify the target cell range Set cellRange = wb.Sheets(1).Range("B5:D10") ' Change this range as needed ' Specify the font name and size fontName = "Arial" ' Change to the desired font fontSize = 15 ' Change to the desired font size ' Loop through all sheets in the workbook For Each ws In wb.Sheets ' Change font properties for the specified range in the current sheet ws.Range(cellRange.Address).Font.Name = fontName ws.Range(cellRange.Address).Font.Size = fontSize ' Add more font properties if needed Next ws End Sub

2 Comments

Shery Aug 1, 2023 at 9:46 PM

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?
- Reply
  Md. Abdur Rahim Rasel Aug 2, 2023 at 11:35 AM
  Hello Shery!
  Thanks for your feedback.
  You can use the below VBA code to change the font and font size in the same cell range across all the sheets in the same workbook.
```
Sub Change_Font_Across_All_Sheets()
	Dim wb As Workbook
	Dim ws As Worksheet
	Dim cellRange As Range
	Dim fontName As String
	Dim fontSize As Integer
    
	' Set the workbook object
	Set wb = ThisWorkbook
    
	' Specify the target cell range
	Set cellRange = wb.Sheets(1).Range("B5:D10") ' Change this range as needed
    
	' Specify the font name and size
	fontName = "Arial" ' Change to the desired font
	fontSize = 15 	' Change to the desired font size
    
	' Loop through all sheets in the workbook
	For Each ws In wb.Sheets
    	' Change font properties for the specified range in the current sheet
    	ws.Range(cellRange.Address).Font.Name = fontName
    	ws.Range(cellRange.Address).Font.Size = fontSize
    	' Add more font properties if needed
	Next ws
End Sub
```
  Please download the Excel file for solving your problem and practice with it.
  Change Font Across All Sheets.xlsm
  If you are still facing issues, please mail us at the address below.
  [email protected]
  Again, thank you for being with us.
  Regards
  Md. Abdur Rahim Rasel
  Exceldemy Team