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

 

[Joe4]

I don't think so.

Usually, you can add the command "Application.Volatile" to the top of the VBA code in your custom function to make it a volatile function. I believe this causes it to re-calculate automatically when any data is changed.
But I do not believe that a color change will trigger that.

 

[Tetra201]

See if this helps.

 

[hiker95]

Guzzlr,

See if the following will help you:

http://www.mrexcel.com/forum/excel-questions/973176-formula-sum-column-fill-colour.html

 

[Guzzlr]

Sorry,
put my answer on the wrong thread...

stuartgb100,

How about something like this?

[COLOR=#333333]
Function SumByColor(CellColor As Range, SumRange As Range)
' SumByColor Function
' <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help;">VBA</acronym> and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 10/30/2016 by hiker95
' =SumByColor(H3,C:C)
' =SumByColor(J3,C:C)
' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + myCell.Value
  End If
Next myCell
SumByColor = myTotal
End Function[/COLOR]

The above code is unfortunately not working either. I still have to click up in the formula bar to reset the colors.
I did run the Macro recorder and this is what it came up with:

Code:
Sub Macro1()'
' Macro1 Macro
'


'
Range("H4").Select
ActiveCell.FormulaR1C1 = "=SumByColor(R[6]C,R[3]C:R[28]C)"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=SumByColor(R[6]C,R[2]C:R[27]C)"
Range("A1").Select
End Sub


This sort of works, it mimics me clicking on H4 & H5 where my formula is and clicking on the formula bar and then on the sheet to reset the formula's in H4 & H5

 

[liveinhope]

I don't think so.

Usually, you can add the command "Application.Volatile" to the top of the VBA code in your custom function to make it a volatile function. I believe this causes it to re-calculate automatically when any data is changed.
But I do not believe that a color change will trigger that.

Hi Joe from liveinhope .. you're right colour change doesn't trigger the function

I'm subscribed to another current thread on this same subject https://www.mrexcel.com/forum/excel-questions/990744-refreshing-worksheet-calculate-formula.html .. thread currently "stuck" awaiting input from original poster
but in short I have been testing the function in that thread and colour change makes no difference.

I also tried your suggestion re adding "application volatile" to top of the function .. results in compile error message "invalid use of property" ..did you mean put "application volatile" in the function or at the top of the module before anything else

At the moment (and I suspect it will be that way forever) using ALT-CTRL-F9 to force complete recalulate

 

[Joe4]

I also tried your suggestion re adding "application volatile" to top of the function .. results in compile error message "invalid use of property" ..did you mean put "application volatile" in the function or at the top of the module before anything else

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

 

[Guzzlr]

Putting this line in:

Application.CalculateFull

at the end of all the entire code seems to be working for me to update the color changes.

 

[Guzzlr]

Function SumByColor(CellColor As Range, rRange As Range)

Dim cSum As Long
Dim ColIndex As Integer


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

One thing odd, is when I use the UDF, and format the cell contents In "Number" to 3 decimal points, the number generated by the UDF does not seem to round correctly.
For example, the column which is summed by the color index should be 1.0672. However, the UDF keeps dispalying the number 1.0000, even when I rerun the program?
Thanks for the help

 

[Joe4]

Its because of this line:

Dim cSum As Long

The variable you are using to track your sum, csum, is declared to be a Long Integer. Long Integers have no decimals portion.
Choose "Double" or "Single".
You should also declare the result of the function, i.e.

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

Dim cSum As [COLOR=#FF0000]Double[/COLOR]
Dim ColIndex As Integer