Using a small routine in Excel VBA you can actually count the number of cells that contain a specific font colour, for example count the number of cells that contain black font.
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) = "" Then GoTo Here
If Cells(A, 1).Font.ColorIndex = 1 Then
Colour = Colour + 1
Let’s break down the routine that counts the number of cells that contain black font.
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) = "" Then GoTo Here – When checking the cells for text colour a blank cell will result in a colour index of 1. So if you were checking for black font, as in this example, you will get erroneous results. So this line of code simply states that if the cell is blank then go to
Here: where it then progresses the next cycle in the For loop.
If Cells(A, 1).Font.ColorIndex = 1 Then – This line is where we check for font 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.
Colour = Colour + 1 – This line adds 1 to the variable Colour if the font is black. If the font is not black then the routine skips this line and progresses to the
End If line.
Here: – This is where the routine comes to if the there is a blank cell. Otherwise it is simply ignored.
Next A – This tells the routine to go back to the beginning and increase the A variable by one.
MsgBox Colour – Once the For loop is complete the routine will display a message box that will give you the total number of cells that contain black font. In my example there were five cells containing black font.
End Sub – This ends the routine.
I hope you found this post useful.