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)
Else
    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.


Enjoy!

5 comments:

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

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

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

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

    ReplyDelete