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 how 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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Ways to Change Font Color Using Excel VBA
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, 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 Code. Here’s an overview of the dataset for today’s task.
1. Use 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. It’s very helpful for some particular moments. 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
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
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Change Font Color will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, the Change Font Color module pops up. In the Change Font Color module, write down the below VBA
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
- 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.
Read More: Excel VBA: Change Font Color for Part of Text (3 Methods)
2. Apply the 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 is 56 numbers code. To modify the font color of any cell, we may pick any of the color codes from 1 to 56. These are given below.
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
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- While running the code, you will be able to change the font color which has been given in the below screenshot.
Read More: [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
Similar Readings
- How to Capitalize the First Letter in Excel (3 Methods)
- Capitalize Each Word in Excel (7 Ways)
- How to Change Case in Excel without a Formula (5 Ways)
- Change Lowercase to Uppercase in Excel Without Formula
- How to Change Font Size of the Whole Sheet with Excel VBA
3. Use the RGB Color Code in VBA Code to Change Font Color in Excel
Last but not the 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. The VBA code is,
Sub Change_Color_Font()
Range("D5:D14").Font.Color = RGB(250, 125, 250)
End Sub
- Further, run the VBA To do that, 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.
Read More: How to Format Cell and Center Text with Excel VBA (5 Ways)
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
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.
Related Articles
- How to Format Text to Capitalize First Letter in Excel (10 Ways)
- How to Convert Text to Time Format with AM/PM in Excel (3 Methods)
- Excel VBA: Format Cell as Text (3 Methods)
- How to Add Leading Zeros in Excel Text Format (10 Ways)
- Format Text in Excel Cell (10 Approaches)
- How to Change Lowercase to Uppercase in Excel (6 Methods)