VBA to Sum Cells By Color

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
982
Office Version
  1. 2021
Platform
  1. Windows
Code:
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
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
Sorry,
put my answer on the wrong thread...


stuartgb100,
How about something like this?
Code:
[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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Code:
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
 
Upvote 0
Its because of this line:
Code:
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.
Code:
Function SumByColor(CellColor As Range, rRange As Range) [COLOR=#ff0000]as Double[/COLOR]

Dim cSum As [COLOR=#FF0000]Double[/COLOR]
Dim ColIndex As Integer
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top