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
ASAPSumByCellColor = N