VBA to Sum Cells By Color

[Guzzlr]

Function SumByColor(CellColor As Range, rRange As Range)Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
        cSum = WorksheetFunction.Sum(cl, cSum)
    End If
Next cl
SumByColor = cSum


End Function

Hello All,
I have the following code to sum cells with a color.
This is working good on my worksheet tab for column range of H8:H4000. with Cell H4 = SumByColor(H9,H8:H4000)
The problem is if the color changes, in any cell, then I must go back and "click" on the newly created color to "refresh" the formula

Is there a way, that every time the macro is run, the color sum in cell H4 will always sum, regardless of which color is used and which cell in H8:H4000 is colored?
Basically, it looks like all I need to have is a code which will refresh the formula in H4, before the macro is run?
Thanks for the help
excel 2010

 

[Guzzlr]

You should also declare the result of the function, i.e.

Function SumByColor(CellColor As Range, rRange As Range) [COLOR=#ff0000]as Double[/COLOR]

Umm...I'm guessing "Declaring the result" part means "As Double" at the Function line?

thanks...I'll have to mow your yard after all this is done!

 

[Joe4]

Umm...I'm guessing "Declaring the result" part means "As Double" at the Function line?

Yes. If you do not do that, it defaults to "Variant" (which pretty much means anything, including String).

 

[Guzzlr]

[COLOR=#574123]Dim cSum As Long
[/COLOR][COLOR=#574123]Dim ColIndex As Integer[/COLOR]

A little confused here, should I have cSum as Double or ColIndex as Double, or Both? Thanks

 

[Joe4]

Whoops, typo. Sorry about that.

I had it backwards.
cSum is the variable doing the summing, so that one we want as Double.
ColIndex just returns the color Index of the column, so decimals are not needed there.

I fixed my previous post to reflect that.

 

[Guzzlr]

OK, Thankyou

 

[liveinhope]

It would be the first live within the function.
But it only fires on data changes.

Hi Joe am I doing something wrong ... I put the "application volatile" as first line the function as per instruction and I still get "compile error . invalid use of property" message

(I understand that the function won't run on change of colour , only on change of data but just wondering about how to use application volatile anyway .. may be useful some other time)

Function SumByColor(CellColor As Range, rRange As Range)
Application Volatile
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum

End Function

 

[Joe4]

It should be:
Application.Volatile

 

[liveinhope]

thanks ! I missed the obvious there !

re the fact that colour change (or any other format change for that matter ) doesn't trigger the function am I correct in my understanding that this is a feature of how some custom functions work ...and that there is no real "solution" to this .
all you can do is be aware that if your workbook contains custom functions you would need to do a Application.CalculateFull or ALT-CTRL-F9 to be sure that your data is up to date

 

[Joe4]

the fact that colour change (or any other format change for that matter ) doesn't trigger the function am I correct in my understanding that this is a feature of how some custom functions work

The issue isn't so much with the custom function as it is how you want this to work.
Autocalc on formulas is triggered on some values being changed on your sheet. Format changes are not value changes, and therefore do not trigger a re-calc.

that there is no real "solution" to this

See the link Tetra201 posted up in post #3. There are some "workarounds". Whether it is worth the trouble/effort is really up to the user.

 

[liveinhope]

Thanks Joe .. I had a quick look at Tetra201's link .. not sure if this work around is something that is applied at the workbook level or "licenced copy of excel" level but either way

I think I'm better off making sure that users are aware that "that's just the way excel works .. "...