Tuesday, March 8, 2011

Do more with less - Excel (Counting colored cells)

This is super fun stuff.  Even need to count cells based on colors?  Check this out.

Function CountByColor(InRange As Range, _
    WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Long
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal the WhatColorIndex.
Dim Rng As Range
Application. Volatile True

For Each Rng In InRange. Cells
If OfText = True Then
    CountByColor = CountByColor -  _
         (Rng.Font.ColorIndex = WhatColorIndex)
    CountByColor = CountByColor -  _
       (Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

All you have to do is the good old fashioned alt+f11, create a new module copy paste the goodness above.  Then in a cell call the function like this: "= countbycolor(range, colorindex#, true false for text or not)"  and you are god to go.  Below if the color index reference chart.



  1. Oh that's really quite useful! Thanks.

  2. Haven't worked with Excel for quite some time now. I didn't know how useful it could be!

  3. not bad, but perhaps choose a different text color next time

  4. Thanks for the feedback on the font color. I will update it next time.