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

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.