Sum by fill color
 

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

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
--
Gary's Student


"Dee" wrote:

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

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.

--
HTH

Bob Phillips

"Dee" wrote in message
...
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

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

Biff

"Dee" wrote in message
...
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