Date and Time

Today I came across one VB question under the Yahoo! Answer section which is about how to count number of items in Excel spreadsheet based on font color. The method is relatively simple, you use If....Then...ElseIf.....Then......End If statement and the colorIndex property of the font object to count text or numbers that make up of different colors. Below is the sample program:

　

Private Sub CommandButton1_Click()
Dim i, counterBlack, counterBlue, counterRed As Integer
For i = 1 To 20

If Cells(i, 2).Font.ColorIndex = -4105 Then
counterBlack = counterBlack + 1
ElseIf Cells(i, 2).Font.ColorIndex = 5 Then
counterBlue = counterBlue + 1
ElseIf Cells(i, 2).Font.ColorIndex = 3 Then
counterRed = counterRed + 1
End If
Next i
Cells(21, 2).Value = counterBlack
Cells(22, 2).Value = counterBlue
Cells(23, 2).Value = counterRed

End Sub

　

　

Explanations

　
　
　
　
The color index for black font is not 0 but strange figure of -4105
The color index for blue font is 5 and the color index for red color is 3
　
Using counterBlack=counterBlack+1 serve as a counter for black color.
similarly, counterBlue=counterBlack+1 serve as a counter for blue color
and counterRed=counterBlack+1 serve as a counter for Red color
　
The results are shown in cells(21,2), cells(22,2), cells(23,2),

VBA program- counting based on font colors