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

Get FREE Advanced Excel Exercises with Solutions!

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.
`=SUBTOTAL(3,B5:B11)` 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. Read More: Excel Formula to Count Cells with Specific 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.

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. Read More: How to Count Colored Cells in Excel with VBA

## 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.

Stay with us and continue to learn. Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

1. Reply I followed the instructions in this one
Run a VBA Code to Count Cells with Specific Text and Fill Color in Excel
but the result is not updating when i edit the range of cells and more cells match the criteria
i want the result to change when i update the table
i think this is supposed to be function not sub in the vba code but im not really sure
i hope you can help me as i need the vba code and the function to run it
thank you

• Reply Greetings DANA,
To update the result after each entry you make, you just need to edit a one-line VBA code. It will update the table range each time you make a new entry in column B.

The VBA Code:

``Set TableRng = Range(Range("B5"), Range("B5").End(xlDown))``

Now, make new entries and run the program to update the result.
Hope this will work for you. Please give us feedback if you have any further queries.

2. Reply Mr Steven J Bunting Feb 8, 2023 at 6:48 PM

Hi, Is there a way to do this but where the fill of your cell has been previously decided by conditional formatting. I have cells which are conditionally formatted a colour, depending upon the text that is in them.

I then want to count how many of these new cells have the colour in and have specific text.

• Reply Hi MR STEVEN J BUNTING,
Greetings. Thanks for commenting. Yes, you can count cells by color with conditional formatting in Excel. To do this, you can follow three procedures: filter feature, table feature, and sort feature. Please follow the following articles if you would like to know more details.
Count Cells by Color with Conditional Formatting in Excel (3 Methods)

3. Reply Hi, thank you for the suggestions.
I have a question:

Is it possible to have the range covering MULTIPLE COLUMNS to perform a VBA Code to Count Cells with Specific Text and Font Color?
Thank you

• Reply Akib Bin Rashid May 24, 2023 at 2:52 PM

Hi JOAO
Yes, you can count cells with specific text and font color in a range covering Multiple Columns using VBA.
I will show you two cases regarding how to count cells by font color of specific text.

Case 1: Count Cells by Font Color of Specific Text
First, let me explain the case a bit. Please have a look at the image. Here, the font color in the specific text “The Prince of the Skies” (in E5) is Red, which is present in both B and C columns. To count the cells that have the same font color of that specific text, you should run the code given below:

``````
Sub CountCellsByFontColor()
Dim rng As Range
Dim lookupCell As Range
Dim count As Long

Set rng = Range("B5:C12")
Set lookupCell = Range("E5")

For Each cell In rng
If cell.Font.Color = lookupCell.Font.Color Then
count = count + 1
End If
Next cell

Range("F5") = count
End Sub
``````

Note:
cell.Font.Color = lookupCell.Font.Color → With this command, Excel is matching the font color of each cell (in Range B5:C12) with the font color of cell E5. If they match, the count variable will increase by 1.

Once you run the code, Excel will show you the result. Case 2: Count Cells with Specific Text and Font Color
Let’s understand this case first. Here, the reference text is “Pippo and Clara”. However, the font color is different in B6 and B8.
Now, to count cells by specific text with color, write down the following code:

``````
Sub CountCellsByTextAndColor()
Dim count As Long
Dim tableRange As Range
Dim lookupText As String
Dim lookupColor As Range
Dim cell As Range
lookupText = InputBox("Specific Text: ")

Set tableRange = Range("B5:C12")

Set lookupColor = Range("E5")

count = 0

For Each cell In tableRange
If cell.Value = lookupText And cell.Font.Color = lookupColor.Font.Color Then
count = count + 1
End If
Next cell
Range("E5") = lookupText
Range("F5") = count

End Sub
``````

Once you run the code, you will get an input box. You have to write the specific text that you desire. Then, on clicking OK, Excel will show the result. Note:
cell.Value = lookupText And cell.Font.Color = lookupColor.Font.Color → This command is checking two conditions
cell.Value = lookupText: This condition checks if the value of the cell is equal to the value of the lookupText variable.
cell.Font.Color = lookupColor.Font.Color: This condition checks if the font color of the cell is the same as the font color of the lookupColor cell.
If both condition match, the count variable will increase by 1.

Here, the count is 1. Because, for the text (“Pippo and Clara”), the font color matches only once in cell B8.
Thank you. Have a good day! Advanced Excel Exercises with Solutions PDF  