Excel VBA Code to Count the Shaded Cells in an Array

There's some very helpful VBA code posted to this site, which will allow you to count the number of cells shaded a particular color in a given array. Enter the below visual basic code in a new module and you'll have a new formula, COUNTCOLORIF to use in Excel.

The first entry in the formula is a cell containing the shading formatting of the cells that you want to count. The second entry in the formula is the array that you want to count cells in. In this example we're reviewing a spreadsheet containing batting data for major league baseball players. The entries for Brooklyn Dodger players have been shaded in blue.

=COUNTCOLORIF(A33167,A:A)

Cell A33167 lists the abbreviated name for Jackie Robinson, or his 1957 rookie season. The formula tells us that 2160 men batted for the Brooklyn Dodgers.

Function CountColorIf(rSample As Range, rArea As Range) As Long Dim rAreaCell As Range Dim lMatchColor As Long Dim lCounter As Long

lMatchColor = rSample.Interior.Color For Each rAreaCell In rArea If rAreaCell.Interior.Color = lMatchColor Then lCounter = lCounter + 1 End If Next rAreaCell CountColorIf = lCounter End Function