Summing Color Filled cells only

[bobgrand]

Hello all,

I have a rather large list of values that are color filled with a varity of different colors. Is there a way to sum only the cells that are color filled?

Any help would be greatly appreciated.

Bob

 

[Cubist]

Try this custom function.

Function SumColorFilled(rng As Range) As Double
    Dim cell As Range
    Dim s As Double
    s = 0

    For Each cell In rng
        If cell.Interior.ColorIndex <> xlNone Then
            s = s + cell.Value
        End If
    Next cell

    SumColorFilled = s
End Function

Book1
	B	C	D	E
13	16	8		25
14	5	9
Sheet4

Cell Formulas
Range	Formula
E13	E13	=SumColorFilled(B13:C14)

 

[bobgrand]

Hi Cubic,

I am fairly new to the Macro/VBA world so please bear with me.

Not sure what I am doing wrong here. I built a quick test sheet just as you show. I Copy and pasted the VBA and closed the editor. Before I highlight any values there is an error in E13 that shows "#NAME?" After I highlight 2 of the 4 cells the error remains "#NAME?" I feel this is so simple I am missing something. Do you have any guidance?

To add to this Highlights can be different from report to report. This report could have 12 different highlights and the next report could have 30.

As always any help is greatly appreciated.

 

[MARK858]

Did you paste the code in a regular module or the worksheet module?

 

[bobgrand]

Thanks Mark858

I didn't insert a module.

 

[MARK858]

You're welcome
Btw, I think the code could do with the line in red below being added

Function SumColorFilled(rng As Range) As Double
    Dim cell As Range
    Dim s As Double
    
    Application.Volatile
    
    s = 0

    For Each cell In rng
        If cell.Interior.ColorIndex <> xlNone Then
            s = s + cell.Value
        End If
    Next cell

    SumColorFilled = s
End Function

 

[Cubist]

While I do agree with @MARK858 that we should add Application.Volatile, note that it'll make the function significantly slower and taxing on your workbook (especially when you have a large list of values). Is there a rule for the highlighted values?

 

[bobgrand]

I am not sure of the rule for the highlighted values. For the most part I can now show them what I learned here to see if it will help them.

Thank you both for you guidance.
I learn a little bit more every day.

 

[Cubist]

You're welcome. Glad we can assist.

 

[bobgrand]

I would like to expand on this if you don't mind.

I am being asked if there is a way to get totals by specific color, Yellow, Green, Blue, Purple, etc..

In column Q there are Dollar Totals. Colors are being used to identify which is most important to least important. These colors can change over time. Can the totals be calculated in Column S, Rows 2,3,4,5,6 as shown in the image attached.

As always thank you for any help that is offered.