How to Count Cells with Specific Text and Fill/Font Color in Excel (4 Ways)

It’s possible that you’ll need to count cells based on specific text or formats, such as fill color and font color. Furthermore, you may be required to meet both conditions. In this tutorial, we will show you some ways to count cells with specific text and fill and font color in Excel.

How to Count Cells with Specific Text and Fill/Font Color in Excel: 4 Best Ways

We’ll go over the four best approaches to count cells with specific text and fill/font color in the sections below. To begin, we’ll use several Excel built-in options, and then we’ll employ VBA programs to complete the process. In the below image, a sample data set is provided which will be used to have the job done.

1. Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

First and foremost, we’ll use the Data tab’s Filter option. Follow the instructions below to do so.

Step 1:

• Select the cell in the column header.

Step 2:

• Go to the Data tab, click on the Filter.

• Therefore, a filter icon will appear.

Step 3:

• In a blank cell, type the following formula.

Step 4:

• First to filter by color, click on the icon first.
• Then, select the Filter by Color.
• Click on a specific color.
• Press the Enter.

• Therefore, you will get the result as 3, as the 3 cells are formatted with the same fill color.

Step 5:

• To count by specific font color, choose the Filter by Font Color.

• As a result, you will the value as 3. As 3 of has the same font color.

2. Use the Find & Select Feature to Count Cells with Specific Text and Fill/Font Color in Excel

From the Editing option in Excel, you can count cells by using the Find & Select command.

Follow the outlined steps below.

Step 1:

• Firstly, click on the Find & select.
• Choose the Find.

Step 2:

• From the Format option, select the Choose Format From cell.

Step 3:

• Select any cell you want to count the formats.
• Then, click on the Find All.
• Finally, you will obtain result 3 shown below the box.

Step 4:

• Now to count cells with specific text font, just click on the cell.
• Then, choose the Find All option to get the result.

3. Run a VBA Code to Count Cells with Specific Text and Fill Color in Excel

Moreover, you can apply VBA codes to do the task. Simply, follow the steps below.

Step 1:

• Press Alt  + F11 to open VBA Macro.
• Click on the Insert.
• Choose the Module.

Step 2:

• Paste the following VBA codes for your table range B5:B12 and lookup value in cell E5.

Sub CountCellsByFontColor()
'Declare variables
    Dim I As Long
    Dim Number As Long
    Dim Rows As Long
    Dim OutputRng As Range
    Dim TableRng, LookupFillColor As Range
    On Error Resume Next
'Set range to variables
    Set TableRng = Range("B5:B12")
    Set LookupFillColor = Range("E5")
'Set a input box for result output
    Set OutputRng = Application.InputBox("select a cell:", "ExcelDemy", Selection.Address, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
'Command to count the rows
    xRows = TableRng.Rows.Count
    Set TableRng = TableRng(1)
'Apply loop
    Number = 0
    For I = 1 To xRows
'Set condition to match the font and count the cells
        If TableRng.Offset(I - 1, 0).Interior.ColorIndex = LookupFillColor.Interior.ColorIndex Then
                Number = Number + 1
        End If
    Next
'command to output the result
    OutputRng = Number
End Sub

Step 3:

• Save the program and press F5 to run it.
• Select any cell where you want to show the result. Here, we select the F5.
• Finally, press the Enter.

• Therefore, the result will show as 4, as the 4 cells from the table have the same fill color format.

Notes: To count cells with both the conditions of specific text and fill/font color just add the following extra condition in the VBA code.

If TableRng.Offset(I - 1, 0).Value = LookupFillColor.Value Then
Number = Number + 1
End If

Step 1:

• So, the paste and run the following final codes.

Sub CountCellsByFillColor()
'Declare variables
    Dim I As Long
    Dim Number As Long
    Dim Rows As Long
    Dim OutputRng As Range
    Dim TableRng, LookupFillColor As Range
    On Error Resume Next
'Set range to variables
    Set TableRng = Range("B5:B12")
    Set LookupFillColor = Range("E5")
'Set a input box for result output
    Set OutputRng = Application.InputBox("select a cell:", "ExcelDemy", Selection.Address, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
'Command to count the rows
    xRows = TableRng.Rows.Count
    Set TableRng = TableRng(1)
'Apply loop
    Number = 0
    For I = 1 To xRows
'Set condition to match the Fillcolor/Interior and count the cells
        If TableRng.Offset(I - 1, 0).Interior.ColorIndex = LookupFillColor.Interior.ColorIndex Then
         If TableRng.Offset(I - 1, 0).Value = LookupFillColor.Value Then
                Number = Number + 1
         End If
        End If
    Next
'command to output the result
    OutputRng = Number
End Sub

Step 2:

• Run the code to see the result. The result show as 2, as there are 2 same cells with the same text and fill color.

4. Perform a VBA Code to Count Cells with Specific Text and Font Color in Excel

To find and count the specific text with font color follow the steps below.

Step 1:

• After opening VBA Module, paste the following codes.

Sub CountCellsByFontColor()
'Declare variables
    Dim I As Long
    Dim Number As Long
    Dim Rows As Long
    Dim OutputRng As Range
    Dim TableRng, LookupFillColor As Range
    On Error Resume Next
'Set range to variables
    Set TableRng = Range("B5:B12")
    Set LookupFillColor = Range("E5")
'Set a input box for result output
    Set OutputRng = Application.InputBox("select a cell:", "ExcelDemy", Selection.Address, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
'Command to count the rows
    xRows = TableRng.Rows.Count
    Set TableRng = TableRng(1)
'Apply loop
    Number = 0
    For I = 1 To xRows
'Set condition to match the font and count the cells
        If TableRng.Offset(I - 1, 0).Font.ColorIndex = LookupFillColor.Font.ColorIndex Then
                Number = Number + 1
        End If
    Next
'command to output the result
    OutputRng = Number
End Sub

Step 2:

• Then, save the program and press F5 to run.
• Select a blank cell you want to get the result.
• Then, click on the OK.

• As a result, the result will appear as 3.

Conclusion

To summarize, I hope you now know how to count cells that have specific text and a specific fill/font color. Your data should be taught and practiced using all of these ways. Examine the practice book and apply what you’ve learned. We are encouraged to continue giving seminars like this because of your critical support.

Please do not hesitate to contact us if you have any queries. Please share your thoughts in the comments box below.

Stay with us and continue to learn.

<< Go Back to Colored Cells | Count Cells | Formula List | Learn Excel