VBA Function (UDF) to sum cells that match a color

I'm trying to write a function that will take two arguments (range,cell). I'd like to sum all of the cells inside the 'range' that match the color inside of 'cell'. See attached for an idea of what I'm talking about. Does anyone have a function that does this?
SumByColor.xlsx

Microsoft Excel

Last Comment

BBlu

8/22/2022 - Mon

SOLUTION

Frank White

3/15/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

kgerb

3/15/2012

Here is one where the color index is not static but dependent on the color index of the cell specified in the 2nd argument.

Function SumByColor(rng As Range, cll As Range) As Double
Dim r As Range
For Each r In rng
    If r.Interior.ColorIndex = cll.Interior.ColorIndex Then SumByColor = SumByColor + r
Next r
End Function

Open in new window

Kyle

Anthony Berenguel

3/15/2012

try this.. although it looks pretty similar to the solution above

Function sumByBGC(r As RANGE, c As String)
    'sum by background color
    Dim result As Long
    result = 0

    Debug.Print c
    For Each cell In r
        Debug.Print cell
        With cell
                If cell.Interior.ColorIndex = RANGE(c).Interior.ColorIndex Then
                result = result + cell.Value
            End If
        End With
    Next
    sumByBGC = result
End Function