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.
📌 Steps
- 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.
📌 Steps
- 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.
Conclusion
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.
