top of page

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


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page