A way to refresh a certain cell everytime wroksheet changes

[Travis Littlechilds]

I'm using a fuction I found on the net called countcolor (included at bottom
of post.) I've got it working on my schedule I'm making, and it counts up how
many closers and suppers and whatnot, but the problem is if I change a cell
and the color changes, the count doesn't change until I go into the cell and
hit enter to reenter the formula, is there a way to refresh this cell
automatically every time the worksheet is updated?


Function CountColor(Rng As Range, RngColor As Range) As Integer

Dim Cll As Range
Dim Clr As Long

Clr = RngColor.Range("A1").Interior.Color

For Each Cll In Rng
If Cll.Interior.Color = Clr Then
CountColor = CountColor + 1
End If
Next Cll

End Function

 

[Arvi Laanemets]

Hi

there are 2 ways.

1.
Function CountColor(Rng As Range, RngColor As Range) As Integer
Dim Cll As Range
Dim Clr As Long

Application.Volatile
.....

2.
Function CountColor(Rng As Range, RngColor As Range,Optional TestTime As
Date) As Integer
Dim Cll As Range
Dim Clr As Long
.....

With second approach, when you want to use the function as volatile, you
enter the formula p.e. in form
=COUNTCOLOR(YourRange, ColorToCount, NOW())
When you dont want the formula to act as volatile, you simply drop 3rd
parameter.