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