Sum by cell color

Last week I got a question from Mike:


Thank you for writing ASAP Utilities. It has saved me countless hours in Excel time and has given me capabilities that I wouldn't otherwise have. I wanted to ask for some help. I noticed that in the user-defined functions for ASAP Utilities there is a function "ASAPSumbyFontColor". I was wondering if you could provide me with the code for a user-defined function that would sum by cell color. I am an intermediate user of Excel with some macro experience but cannot figure out how to create this function.

Whatever help you could provide would be much appreciated.

Mike, good idea! I will adapt this new function in the next version. Until then:

Here's the code:

Public Function ASAPSumByCellColor(rngSource As Range, _
  sColorIndex As Single) As Single
  ' sums all cells within a range that have a certain
  ' background color (colorindex)
  Dim rngCel                                 As Range
  N = 0
  For Each rngCel In rngSource
    If rngCel.Interior.ColorIndex = sColorIndex Then
      If IsNumeric(rngCel.Value) Then N = N + rngCel.Value
    End If
  ASAPSumByCellColor = N
End Function



Firstly, thank you so much for asap utilities, I have downloaded several different peoples utilities, and paid for some of them, but the only one I regularly use is asap utilities.

As for summing by colour, you might want to look at the ozgrid page on this. I use this method as do several others in my office, so it does work.


I don't think anything better and more valuable could have been possible for an extensive excel user like me than ASAP utility
Its been a great contribution to overall Excel users and me in particular