How to Count Blank Colored Cells in Excel (2 Methods)

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.

Download Practice Workbook

You can download the practice workbook from the download button below.

2 Methods to Count Blank Colored Cells in Excel

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. Count Blank Colored Cells in Excel Using Find & Select

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.

Read More: How to Count Colored Cells in Excel (4 Simple Ways)

2. Count Blank Colored Cells in Excel with VBA

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 (4 Easy Methods)

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.

Conclusion

Now you can easily count blank colored cells following these methods. Please use the comment section below for further queries or suggestions. You can also visit our ExcelDemy blog to learn more about excel. Stay with us and keep learning.

Related Articles

• How to Count Cells with Specific Text and Fill/Font Color in Excel (4 Ways)
• Excel Formula to Count Colored Cells in a Row (2 Effective Methods)
• Count Cells by Color with Conditional Formatting in Excel (3 Methods)
• Excel Formula to Count Cells with Specific Color (4 Ways)
• How to Count Colored Cells In Excel Without VBA (3 Methods)