sum by color

[ca123]

sir, I have one macro code to sum by color, but the problem is that the code does not auto change the total when the color changes. the code is as follows :

Function SumIFbyColor(rColor As Range, rCells As Range)
'Parameter rColor is used for a cell from your worksheet
'formatted with the same color like color desired from the table.
'Parameter rCells is used for a cell range from your worksheet
'that can contain cells formatted with the same color of font or background
'like referenced cell.

'Defining variables
Dim rRange As Range
Dim sumColor As Long


'Initiating sumColor
sumColor = 0


' Parsing cells from range
For Each rRange In rCells
If rRange.Font.Color = rColor.Font.Color Or rRange.Interior.Color = rColor.Interior.Color Then
sumColor = sumColor + rRange.Cells.Value
End If
Next rRange

' Return results
SumIFbyColor = sumColor

End Function

can you make it to auto change ?

Thanks

[nathansav]

force a recalc activesheet.calculate, on the Worksheet_change event, not sure if making it volatile, application.volatile, would be triggered off a re-format.

[shg]

There is no way to make it automatically recalculate when you manually format a cell to change color. Color is not data to Excel.

[ca123]

@nathansav



i am new to macros, I don't know how to edit this one......

can u please edit the above macro ?