How to count cells with specific text and fill/font color in Excel?
It may be easy for you to count number of cells that contain only one condition, such as count all cells with specific text or font/fill color. However, do you know how to count cells with multiple conditions? For example, count number of cells that contain both specific text and font/fill color. This article will show you the solution.
Count cells with specific text and fill color with VBA code
Count cells with specific text and font color with VBA code
Count cells with specific text and fill color with VBA code
Supposing you have a table as below screenshot shown, for counting cells in column B based on the text and fill color of cell E2, please apply the following VBA code.
1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy below VBA code into the Module window.
VBA code: Count cells with specific text and fill color
Sub CountFillColorValue()
'Update by Extendoffice 20180702
Dim I As Long
Dim xNum As Long
Dim Rows As Long
Dim xRgD As Range
Dim xRg, xRgS As Range
On Error Resume Next
Set xRg = Range("B2:B9")
Set xRgS = Range("E2")
Set xRgD = Application.InputBox("Please select a cell:", "KuTools for Excel", Selection.Address, , , , , 8)
If xRgD Is Nothing Then Exit Sub
xRows = xRg.Rows.Count
Set xRg = xRg(1)
xNum = 0
For I = 1 To xRows
If xRg.Offset(I - 1, 0).Interior.ColorIndex = xRgS.Interior.ColorIndex Then
If xRg.Offset(I - 1, 0).Value = xRgS.Value Then
xNum = xNum + 1
End If
End If
Next
xRgD = xNum
End SubNote: In the code, B2:B9 is the range you will count cells inside, E2 contains the text and fill color you will count cells based on. You can change them to your need.
3. Press the F5 key to run the code, then a Kutools for Excel dialog box pops up, please select a cell to place the result and then click the OK button. See screenshot:
Then the count result is showing in the selected cell immediately.
Count cells with specific text and font color with VBA code
For counting cells in range B2:B9 based on the text and font color of cell E2, please do as follows.
1. Open the Microsoft Visual Basic for Applications window by pressing the Alt + F11 keys.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy below VBA code into the Module window.
VBA code: Count cells with specific text and font color
Sub CountFontColorValue()
'Update by Extendoffice 20180702
Dim I As Long
Dim xNum As Long
Dim xRows As Long
Dim xRgD As Range
Dim xRg, xRgS As Range
On Error Resume Next
Set xRg = Range("B2:B9")
Set xRgS = Range("E2")
Set xRgD = Application.InputBox("Please select a cell:", "KuTools for Excel", Selection.Address, , , , , 8)
If xRgD Is Nothing Then Exit Sub
xRows = xRg.Rows.Count
Set xRg = xRg(1)
xNum = 0
For I = 1 To xRows
If xRg.Offset(I - 1, 0).Font.ColorIndex = xRgS.Font.ColorIndex Then
If xRg.Offset(I - 1, 0).Value = xRgS.Value Then
xNum = xNum + 1
End If
End If
Next
xRgD = xNum
End SubNote: In the code, B2:B9 is the range you will count cells inside, E2 is the criteria that you will count cells based on. You can change them as you need.
3. Press the F5 key to run the code, then a Kutools for Excel dialog box pops up, please select a cell to place the result and then click the OK button. See screenshot:
You will get the result as below screenshot shown:
Related articles:
- How to count number of cells between two values or dates in Excel?
- How to count cells with specific text in selection in Excel?
- How to count if cell contains text or part of text in Excel?
- How to count number of certain/specific words in a cell or a range of cells in Excel?
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
