Sum by fill color

[Guest]

I found a site that showed me a way to have excel sum cells that have only a
particular color. I inserted the following code into a module:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As
Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
'Sums or counts cells based on a specified fill color.
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

I used the following in the cell =ColorFunction(B2, B2:B30). The first time
I entered the function it worked fine. However, when I applied the color in
B2 to another cell in the range, Excel did not recalculate. If I double click
in the cell I entered the function and then hit enter it will recaluclate. Is
there a way to have excel automatically recalculate. I checked in Tools
Options to see if manual calculation was on but it was on automatic.

Thanks very much for your help.

Best regards,

Dee

 

[Guest]

The problem is not yours.

The logic in Excel that decides it times to re-calculate a function is not
sensitive to color changes. For example this tiny bit of VBA:

Function clr(R As Range) As Integer
With R.Interior
clr = .ColorIndex
End With
End Function

will return a number for the color of the cell in the argument. If you
change the color, the function does not automatically update.

CNTRL-ALT-F9 will force an update

 

[Bob Phillips]

That is because a colour change does not trigger a worksheet recalculation.
If you add

Application.Volatile

at the start of the code, and F9 etc after changing a colour will force a
sheet recalculation.

 

[Biff]

That's the reason why you should NOT use cell formatting as the basis of
calculations!

Biff