Home |
Search |
Today's Posts |
#1
|
|||
|
|||
A way to refresh a certain cell everytime wroksheet changes
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 |
#2
|
|||
|
|||
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. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Travis Littlechilds" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL - Auto Refresh Upon Cell Value Change | Excel Worksheet Functions | |||
how to create a variable column in cell reference | Excel Worksheet Functions | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |