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:
For A = 1 To 10 Step 1
If Cells(A, 1).Interior.ColorIndex = 1 Then
ColourFill = ColourFill + 1
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.