This article shows 2 ways how to count blank colored cells in excel. Imagine, you have a list of orders from customers of your store. You need to deliver the items within a certain deadline. The cells in the delivery status column are coded in colors instead of text. Green, Yellow and Red indicate the status as delivered, pending and not delivered respectively. If you want to know how many orders are yet to be delivered, you need to count the red colored cells. This article will help you to do that easily. The following picture gives an idea of the purpose of this article.
Here, I’m going to illustrate the methods on how to count blank colored cells in excel. We will use the following dataset to highlight the methods.
1. Using Find & Select to Count Blank Colored Cells in Excel
You can easily count blank colored cells in excel using the Find & Select feature. Follow the steps below to be able to do that.
- First, select Find & Select >> Go To Special from the home tab. This will pop up the Go To Special dialog box.
- Then, mark the radio button for Blanks and hit OK.
- After that, the dataset will look as follows.
- Next, select Find & Select >> Find. This will open the Find and Replace dialog box.
- After that, click on Format in the Find and Replace dialog box as shown below. This will open the Find Format dialog box.
- Then, go to the Fill tab in the Find Format dialog box as shown in the following picture. After that, select the desired color and hit OK.
- You can also choose the color from the Choose Format From Cell… button.
- Now, select the Find All button. Finally, you will see all the blank colored cells listed as follows.
2. Applying Excel VBA to Count Blank Colored Cells
You can also use VBA as an alternative to the earlier method. To do that, you need to follow the steps below.
- First, press ALT+F11 (on Windows) or Opt+F11 (on Mac) to open the Microsoft Visual Basic for Applications window.
- Then, select Insert >> Module to open a new module.
- After that, copy the following code.
Sub CountBlankColoredCells() Dim Cell As Range Dim Color As Integer Dim CountColor(56) As Long ActiveSheet.Range("D5:D10").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select For Each Cell In Selection With Cell.Interior If .Pattern <> xlNone Then If .ColorIndex <> xlNone Then If IsEmpty(Cell) Then CountColor(.ColorIndex) = _ CountColor(.ColorIndex) + 1 End If End If End If End With Next Cell Result = "The count for blank colored cells:" & vbCrLf & vbCrLf For Color = 0 To 56 If CountColor(Color) > 0 Then Result = Result & "Color " & Color & ": " & CountColor(Color) & vbCrLf End If Next Color MsgBox Result End Sub
- Now, paste the copied code on the blank module.
- Then, run the code using the Run icon or the Run tab. You can also press F5 to do that.
- Finally, you will see the count of different blank colored cells listed as follows.
- If you want to know the total number of blank colored cells, then use the following code instead.
Sub CountBlankColoredCell() Dim Cell As Range Dim CellCount As Long CellCount = 0 ActiveSheet.Range("D5:D10").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select For Each Cell In Selection If Cell.Interior.Pattern <> xlNone Then If Cell.Interior.ColorIndex <> xlNone Then If IsEmpty(Cell) Then CellCount = CellCount + 1 End If End If Next Cell MsgBox "The Number of Blank, Colored Cells is : " & CellCount End Sub
- Now run the code and you will get the following result.
Read More: How to Count Colored Cells in Excel with VBA
Things to Remember
- The first method does not take into consideration whether the cells are formatted conditionally.
- You can change the cell ranges in the code if you are using a different dataset.
Download Practice Workbook
You can download the practice workbook from the download button below.
Now you can easily count blank colored cells following these methods. Please use the comment section below for further queries or suggestions. Stay with us and keep learning.