2 Methods to Count the Number of Cells with Background Colors in Your Excel

In your Excel worksheet, you will certainly highlight some cells by formatting them with background colors. And here we will demonstrate two methods to count the number of those cells with background colors.

There are many different methods that you can do to highlight certain important cells. And formatting cells with background colors is one of the most effective ways. But sometimes you need to count the number of those special cells. The only difference between them with other cells is the background colors. However, sometimes you will use different color for different cells. The image below shows such an example.Example to Count the Number

There are several cells and you can certainly count one by one. But when there are hundreds of cells, you need to use other methods.

Method 1: Define Name

In this methods, you will use the define name in the worksheet.

  1. Select a blank cell in the worksheet. Here we will choose the cell C2.
  2. And then right click this cell.
  3. After that, you will see a new menu. In this menu, choose the option “Define Name”.
  4. In this step, input a name into the first text box. We will use the “CountName” in it.
  5. And then input the formula into the “Refers to” text box:


In this formula, you need to change the second argument according to the actual worksheet.New Name

  1. And then click “OK” to save the name.
  2. Now input this formula into the cell C2 that you have selected:


You need to change it into the name that you have set in step 4.

  1. And then press the button “Enter” on the keyboard. You will see a number in the cell.
  2. After that, double click the fill handle of the cell C2 and fill the formula in the whole column.

In this column, there are different numbers. You will find that if the original cell has a background color, there will be a number. Otherwise the number is 0.

  1. In this step, you need to count the numbers except 0. Input this formula into another cell:


  1. And then press the button “Enter”. Thus, you will get the result in the worksheet.


Method 2: Use Excel VBA

Except for using define name and the COUNTIF function, you can also use VBA macro to count the number of cells.

  1. Press the shortcut keys “Alt +F11” on the keyboard. To open the Visual Basic editor.
  2. And then insert a new module in the editor.Insert Module
  3. Now input the following codes into the new module:
Sub CountCellsWithBackgroundColor()
  Dim nRowIndex As Integer, nCellNumber As Integer

  '  Go through the range
  For nRowIndex = 2 To 13
    If Range("B" & nRowIndex).Interior.ColorIndex <> -4142 Then
      nCellNumber = nCellNumber + 1
    End If
  Next nRowIndex

  '  Output the result
  Range("C2") = nCellNumber
End Sub

Here we will input the result in cell C2. You can also modify certain elements in the codes according to your need.

  1. Next run this macro. You can press the button “F5” on the keyboard to run it.


A Comparison of the Two Methods

Here we have listed all the advantages and disadvantages of the two methods. You can refer to the table below.


Define Name

Use Excel VBA


1.      If you are not familiar with Excel VBA, you can use this method in the worksheet.

2.      Compared with the second, this method is easy to understand and use.

1.      By just one click, you can get the result easily.

2.      You can also use this macro in other files by simply changing certain elements.


1.      There are more steps in this method. Every time you need to count cells, you have to go through all the process.

2.      Using this method will also mess up the worksheet.

1.      When you are not familiar with Excel VBA, you will meet with errors when modifying and running the codes.

2.      Using VBA will sometime make the task more complex.

Therefore, if you are good at VBA, you can use the second method. When you are not sure about the VBA codes, you can use the first method. Either of the method can help you finish your task.

When You Experience Data Loss

When you experience data loss, you will certainly worry about your data and information in Excel files. But with a powerful tool at hand, you can rest assured. This tool can repair corrupted xlsx data and fix other errors in Excel. Thus, you don’t need to worry about those precious data and information.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx file corruption and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.