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

In the dataset below, we have two columns: Book Name and Status. To the right, we have a separate column that will display cell count.

Method 1 – Apply Filter and the SUBTOTAL Function 

Steps:

• Select the cell in the column header.

• Go to the Data tab and click on Filter.

• A filter icon will appear.

• In a blank cell, enter the following formula:

• To filter by color, click on the icon.
• Select Filter by Color.
• Click on a specific color.
• Press Enter.

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

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

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

Method 2 – Use the Find & Select Feature

Step:

• Click on Find & select.
• Choose Find.

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

• Select any cell where you want to count the formats.
• Click on Find All.
• You will get 3, as shown below.

• To count cells with specific text font, click on the cell.
• Choose Find All to get the result.

Method 3 – Run a VBA Code

Steps: 

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

• Enter the following VBA codes for your table range B5:B12 and look up the 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

• Save the program and press F5 to run it.
• Select any cell where you want to show the result. (i.e., F5.)
• Press Enter.

• 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 add the following extra condition in the VBA code.

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

• Enter the following 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

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

Method 4 – Perform a VBA Code

Steps:

• Open the VBA Module
• Enter 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

• Save the program and press F5 to run.
• Select a blank cell where you want to get the result.
• Click on OK.

• The result will appear as 3.

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