VBA Font Color

What is Excel VBA Font Color?

In Excel VBA (Visual Basic for Applications), you can use the Font.Color property to manipulate the font color of text within cells. This property allows you to change the color of the text in a cell, making it more visually appealing or to highlight specific information.

In this example, we use VBA font color codes using RGB to change the font color in cell “A1.”

It changes the font color to red. After running the VBA code, you can see the font color change, as seen below.

How to Change Font Color using VBA?

To know how to perform VBA Font Color automatic based on the given conditions, follow the steps given below.

Step 1: Create a new Workbook. In that, go to the “Developer” section on the Excel toolbar. Then, in the developer tab, select “Visual Basic.”

It will open the VBA (Visual Basic for Applications) Editor. In the title bar, click the “Insert” button and select the “Module” option.

In this example, you want to separate the positive and negative numbers in the given range. It can be done using the VBA Font Color property.

Step 2: Define a sub-procedure to categorize positive and negative numbers.

Step 3: Declare a Worksheet variable and set it up with the sheet this sub-procedure will be working in.

Step 4: Initialize a range for the table to be working on. Specify the worksheet it will be working in with the variable defined earlier.

Step 5: Begin declaring a FOR loop through the range.

Declare an If – Else statement in VBA checking if the cell value is less than 0, then set the VBA Font Color codes as red for that cell value.

Declare an Else condition for positive values and keep the VBA Font Color black, as shown below.

Code:

Sub ChangeFontColorBasedOnCondition()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“Sheet3”)

    Dim trange As Range

    Set trange = ws.Range(“B1:B10”)

    For Each cell In trange

        If cell.Value < 0 Then

            cell.Font.Color = vbRed

        Else

            cell.Font.Color = vbBlack

        End If

    Next cell

End Sub

Step 7: Run the VBA code by pressing “F5” or the green button on the toolbar in the VBA Editor.

Go back to the worksheet to view the output.

Let’s see some exciting examples that showcase the different ways in which VBA Font Color codes can be changed.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

See the different ways in which the Font.Color property is used in the below examples.

Example #1 – VBA Font Color using Color index

Given a set of values, we need to convert them into different colors using the VBA Font.ColorIndex function(Color Index). It is a known fact in Excel VBA that there are about 56 color indices. This can be viewed using a FOR loop running through the rows.

Step 1: Start by naming the subroutine to show all 56 font colors.

Step 2: Name the worksheet this subroutine will be working on.

Step 3: Set the range of the values this subroutine will be working in.

Step 4: Initialize an iterative variable and start a FOR-loop.

Step 5: Check if the cell values are less than or equal to 56 and fill them according to the values given in the cells.

You can also optionally bolden the font for greater visibility.

Code:

Sub ShowAllFontColorNumbers()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“Sheet4”)

    Dim targetRange As Range

    Set targetRange = ws.Range(“A1:D14”)

    Dim c As Range

    For Each c In targetRange

        If c.Value <= 56 Then

            c.Font.ColorIndex = c.Value

            c.Font.Bold = True

        End If

    Next c

End Sub

Step 6: Run the above code by pressing F5. After running the code, go back to the worksheet to view the output.

From the above table, we can infer that:

• VBA Font Color Black Index is 1.
• VBA Font Color White Index is 2.

Example #2 – VBA Font Color Using ColorProperty

Given a table with positive, negative, zero and non-numeric values, you can categorise each of the value types into different font colors.

Step 1: Define a sub-procedure to change the font color based on different categories.

Step 2: Define a worksheet variable and initialize it to the worksheet this sub-procedure will be working in.

Step 3: Define the range in which the subroutine will be working.

Step 4: Initialize an iterative variable and use it to initialize a FOR-loop.

Step 5: Check if the value is numeric, that is, a number. Then, initialize a double VBA data type and assign it to the integer value of the cell value.

The Val() function in Excel VBA is used to convert a string or variant datatype into a floating point value with precision.

Step 6: Start with a Switch Case to check multiple conditions with the variable assigned in the previous step.

Step 7: Define the conditions in the switch case.

• If the value is less than 0, then the font color is changed to magenta.
• If the value is equal to 0, then the font color changes to blue.
• If the value is greater than 0, then the font color changes to green.
• If none of the conditions are fulfilled (non-numeric values), the font color stays the same, that is, black.

Step 8: End the If condition for the IsNumeric function and continue the FOR-loop.

Code:

Sub ChangeFontColorBasedOnCategories()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“Sheet1”)

    Dim targetRange As Range

    Set targetRange = ws.Range(“A1:A10”)

    Dim cell As Range

    For Each cell In targetRange

        If IsNumeric(cell.Value) Then

            Dim cellValue As Double

            cellValue = Val(cell.Value)

            Select Case cellValue

                Case Is < 0

                    cell.Font.Color = vbMagenta

                Case 0

                    cell.Font.Color = vbBlue

                Case Is > 0

                    cell.Font.Color = vbGreen

                Case Else

                    cell.Font.Color = vbBlack

            End Select

        End If

    Next cell

End Sub

Step 9: Run the sub-procedure and then go to the worksheet to view the output.

Example #3 – VBA Color Property with RGB Function

Here is an example where you need to highlight the “Critical” activities to prioritize them. It can be done by using Font Color with RGB Function in VBA.

Step 1: Define a sub-procedure to change the font color of “critical” activities.

Step 2: Name the worksheet this sub-procedure will be working on.

Step 3: Define the range of the table in which this sub-procedure will be working.

Step 4: Define the text to be searched to change the font color for in a string variable.

Step 5: Define a for-loop to run through the table.

Step 6: Check if the cell value corresponds to the text being searched for using an If-Else statement.

If it is true, change the VBA Font color to red.

Step 7: Initialize an Else condition; otherwise, change the fonts in the other cells to green.

Code:

Sub ChangeFontColorBasedOnText()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“Sheet5”)

    Dim targetRange As Range

    Set targetRange = ws.Range(“A1:A10”)

    Dim searchText As String

    searchText = “Critical”

    For Each cell In targetRange

        If cell.Value = searchText Then

            cell.Font.Color = RGB(255, 0, 0)

        Else

            cell.Font.Color = RGB(0, 255, 0)

        End If

    Next cell

End Sub

Step 8: Run the above VBA code. Then, go to the worksheet and see the output.

Important Things To Note

• Ensure the data in the cells you’re modifying with font colors is valid and consistent to avoid issues arising from incorrect or unexpected data.
• Select font colors that offer sufficient contrast against the cell’s background color to maintain readability. Inadequate contrast can make data difficult to interpret.
• Be aware that color choices may render differently on various Excel versions or when printed. Test your code across different platforms to ensure consistency.
• Optimize your VBA code for font color changes to execute efficiently, particularly when working with a substantial number of cells. Inefficient code can slow down your Excel application.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Font Color. with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to What is Excel VBA Font Color. Here we explain the How to Change Font Color using VBA along with examples & downloadable excel template. You can learn more from the following articles –

• VBA Format Date
• VBA Charts
• VBA Conditional Formatting