Counting Excel Cells Based on the Background Colour

Using Excel VBA you can actually count the number of cells that contain a specific background colour.

First step is to create a routine and put it within a module. Once you have done this the routine can be called from the worksheet. So simply copy the code below and paste it into a module:

Sub ColourCount()

For A = 1 To 10 Step 1

If Cells(A, 1).Interior.ColorIndex = 1 Then
ColourFill = ColourFill + 1
End If
Next A

MsgBox ColourFill

End Sub

Let’s break down the routine that counts the number of cells that have a black background.

For A = 1 To 10 Step 1 – We have declared A as a variable because want to go from row one to row ten. So A will become a number between one and ten as the routine progresses. It will increase in value by one every time a loop is performed.

If Cells(A, 1).Interior.ColorIndex = 1 Then – This line checks for the cell background colour. Black has a colour index of 1. Remember that the A in the brackets does not represent column A, the 1 does. A represents the row number that will increase each time a loop is performed.

ColourFill = ColourFill + 1 – This line adds 1 to the variable ColourFill if the cell background colour is black. If it is not black then the routine skips this line and progresses to the End If line.

Next A – This tells the routine to go back to the beginning and increase the A variable by one.

MsgBox ColourFill – Once the For loop is complete the routine will display a message box that will give you the total number of cells that have a black background.

End Sub – This ends the routine.

You can also create an Excel VBA routine that will count the number of cells based upon the font colour..

I hope you found this post useful.

One thought on “Counting Excel Cells Based on the Background Colour

  1. Pepe Agius

    If I have a bit of code like
    Function CellColour(Irow As Integer, Icol As Integer) As Long
    CellColour = Cells(Irow, Icol).Interior.ColorIndex
    End Function

    But I would like this to change that cell condition if the cell background color cahnges this above only work when you press the enter button once you are in the cell.

Leave a Reply

Your email address will not be published. Required fields are marked *