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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

How to count blank colored cells in excel : Dataset


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.

How to count blank colored cells in excel using Find & Select


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.

How to count blank colored cells in excel using VBA

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

How to count blank colored cells in excel using VBA

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo