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

Get FREE Advanced Excel Exercises with Solutions!

While working with large Microsoft Excel, sometimes we need to change the font color. We can easily do that by using the VBA Macros. Applying a VBA code to change the font color is an easy task. Today, in this article, we’ll learn three quick and suitable ways to use VBA code to change font color in Excel effectively with appropriate illustrations.


Excel VBA to Change Font Color (Quick View)

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

vba code to change font color


Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The Name of the sales representatives, their Identification Number, the type of products, and the revenue earned by the sales representatives are given in columns B, C, D, and E respectively. From our dataset, we will change the font color using VBA Code. To do that, we will apply the color constants, RGB command, and Color Index command in VBA codes. Here’s an overview of the dataset for today’s task.

vba code to change font color


1. Using Color Constants in VBA Code to Change Font Color in Excel

Now I’ll show how to change font color by using a simple VBA code with the color constants command. There are eight types of color constants that you can use in your VBA code. They are:

  1. vbRed: Red
  2. vbGreen: Green
  3. vbMagenta: Magenta
  4. vbBlack: Black
  5. vbBlue: Blue
  6. vbWhite: White
  7. vbYellow: Yellow
  8. vbCyan: Cyan

Let’s follow the instructions below to change the font color!

Step 1:

  • First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Use Color Constants in VBA Code to Change Font Color in Excel

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Change Font Color will instantly appear. From that window, we will insert a module for applying our VBA code. To do that, go to, Insert → Module

Step 2:

  • As a result, a new module will open up. In that module, write down the following VBAcode:
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

Use Color Constants in VBA Code to Change Font Color in Excel

  • After that, run the VBA.  To do that, go to, Run → Run Sub/UserForm

  • After running the VBA Code, you will be able to change the font color which has been given in the below screenshot.

Use Color Constants in VBA Code to Change Font Color in Excel

Read More: Excel VBA: Change Font Color for Part of Text


2. Applying Color Index Command to Change Font Color with Excel VBA

Now, we will use the Color Index command in VBA Code to change the font color in Excel. Microsoft has assigned distinct numbers to various colors. There are 56 number codes. To modify the font color of any cell, we may pick any color codes from 1 to 56. These are given below.

Apply the Color Index Command to Change Font Color with Excel VBA

Let’s follow the steps below to change the font color!

Step 1:

  • According to method 1, insert a new module and type the below VBA code. We will change the font color from black to red. To do that, we will apply 3 as the color index code which will give red color font. The VBA code is,
Sub VBA_to_Change_Font_Color()
Range("C5:C14").Select
Selection.Font.ColorIndex = 3
End Sub

  • Now, to run the VBA, go to Run → Run Sub/UserForm

Apply the Color Index Command to Change Font Color with Excel VBA

Step 2:

  • While running the code, you will be able to change the font color which has been given in the below screenshot.

Apply the Color Index Command to Change Font Color with Excel VBA


3. Inserting RGB Color Code in VBA Code to Change Font Color

Last but not least, you can also use the RGB color code to change the font color using the VBA code in Excel. RGB stands for red, green, and blue, and it’s a color code that may be used to make a custom color. Let’s follow the instructions below to change the font color!

Step 1:

  • First, insert a new module according to method 1 and type the below VBA code.
Sub Change_Color_Font()
Range("D5:D14").Font.Color = RGB(250, 125, 250)
End Sub

Use the RGB Color Code in VBA Code to Change Font Color in Excel

  • Now, to run the VBA, go to Run → Run Sub/UserForm

Step 2:

  • After running the VBA Code, you will be able to exchange the font color of column D which has been given in the below screenshot.

Use the RGB Color Code in VBA Code to Change Font Color in Excel

Read More: How to Change Font Size of the Whole Sheet with Excel VBA


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to File → Option → Customize Ribbon


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to exchange the font color with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

2 Comments
  1. 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 Avatar photo
      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo