VBA to Check If Font Color is Red Then Return Results in Excel

Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you if the font color in your dataset is red then how to get certain results in Excel using the VBA.

Download Workbook

You can download the free practice Excel workbook from here.

6 Methods to Check If Font Color Is Red Then Return Certain Results Using VBA in Excel

Look at the following dataset where there are some values that have the font color red. In this section, we will discuss step-by-step procedures if the font color is red then how to get certain results in Excel.

1. If Font Color is Red Then Return Certain Word in Excel

We want to return Yes if the font color is red and No if the font color is not red. Let’s see how to do that with Excel VBA.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Function FontColorRed(target As Range)
    Application.Volatile
    If target.Font.Color = 255 Then
    FontColorRed = "Yes"
    Else
    FontColorRed = "No"
    End If
End Function

This is not a Sub Procedure for the VBA program to run, this is creating a User Defined Function (UDF). So, after writing the code, instead of clicking the Run button from the menu bar, click Save.

• Now go back to the worksheet of interest and write the function you just created with VBA code (Function FontColorRed in the first line of the code) and inside the parentheses of the FontColorRed function, pass the cell reference number that you want to convert to the letter (in our case, we pass Cell C5 inside the parentheses).

So our final formula refers,

=FontColorRed(C5)

• Press Enter.

You will get Yes if the font color in Cell C5 is red, otherwise, you will get No. In our case, the font color of the text inside Cell C5 was not red, so we got No.

• Now drag the row down by Fill Handle to apply the UDF to the rest of the cells and you will get Yes beside the cells that hold text with red font color.

2. Return the Color Code If Font Color Is Red

Here we will learn how to extract the color code based on the font color red in Excel with VBA.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Function ColorCode(rng As Range)
    ColorCode = rng.Font.ColorIndex
End Function

• Now, as shown before, call the ColorCode UDF from the dataset, pass the cell reference number (e.g. C5) as the argument, press Enter.

You will get the associated color code as the return value.

• Now drag the row down by Fill Handle to apply the UDF to the rest of the cells to get the code of font color red.

3. If Font Color is Red Then Return the Index in Excel

Here we will learn how to return the index number based on the font color red in Excel with VBA.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Function IndexColor(cell As Range)
        IndexColor = cell.Font.Color
End Function

• Now, as shown before, call the IndexColor UDF from the dataset, pass the cell reference number (e.g. C5) as the argument, press Enter.

You will get the associated index number of the font color as the return value.

• Now drag the row down by Fill Handle to apply the UDF to the rest of the cells to get the index number of the font color red.

The cells that hold text with red color font will return 255 as the index number of color red is 255.

4. Return the RGB Code If Font Color Is Red in Excel

Here we will learn how to get the RGB code based on the font color red in Excel with VBA.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Function FontRGB(cell As Range)
    Dim iColorIndex As Long
    Dim iColor As Variant
    iColorIndex = cell.Font.Color
    iColor = iColorIndex Mod 256
    iColor = iColor & ", "
    iColor = iColor & (iColorIndex \ 256) Mod 256
    iColor = iColor & ", "
    iColor = iColor & (iColorIndex \ 256 \ 256) Mod 256
    FontRGB = iColor
End Function

• Now, as shown before, call the FontRGB UDF from the dataset, pass the cell reference number (e.g. C5) as the argument, press Enter.

You will get the associated RGB code of the font color as the return value.

• Now drag the row down by Fill Handle to apply the UDF to the rest of the cells to get the RGB code of the font color red.

The cells that hold text with red color font will return 255,0,0 as the RGB code of the color red is 255,0,0.

5. If Font Color is Red Then Highlight the Cell

If you want to highlight the cells that hold the text with the font color red, then keep reading the article to know how to do that.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub HighlightCell()    
    Set ws = Sheets("Highlight") 'set the worksheet    
    For r = 1 To 104
        For c = 1 To 36
            If (ws.Cells(r, c).Font.Color = 255) Then
                'set the desired color index
                ws.Cells(r, c).Interior.ColorIndex = 34
            End If
        Next c
    Next r
End Sub

Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will see that the cells that have red-colored font are now highlighted.

6. Change the Font Color If Font Color Is Red

If you want to change the font color red back to the default color, then follow the steps shown below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub ChangeFontColor()
    With Application.FindFormat.Font
        .Subscript = False
        .Color = 255
        .TintAndShade = 0
    End With
    With Application.ReplaceFormat.Font
        .Subscript = False
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

Your code is now ready to run.

• Run the macro.

The values with the font color red are now turned back into the default font color.

Conclusion

This article showed you if the font color of the values in your dataset is red then how to get certain results in Excel using the VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.