Sum by cell color
Last week I got a question from Mike:
Bastien,
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. Untill then:
Here's the code:
sColorIndex As Single) _
As Single
' sums all cells within a range that have a certain
' background color (colorindex)
Dim rngCel As Range
Application.Volatile
N = 0
For Each rngCel In rngSource
If rngCel.Interior.ColorIndex = sColorIndex Then
If IsNumeric(rngCel.Value) Then N = N + rngCel.Value
End If
Next
ASAPSumByCellColor = N
End Function
Filed under: ASAP Utilities, General on September 19th, 2005 by Bastien |
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.
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
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