Re: Sum by fill color

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Application.Volatile

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

--
HTH

Bob Phillips

"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7EFA1659-B677-457B-ACDD-A1155AD1AFE8@xxxxxxxxxxxxxxxx
> 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


.

Quantcast