Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refresh - Value not refresh (URGENT)
Hi,
I am counting the cell which background color is red. and each cell i m useing conditional formate if value less than target it means cell background color is red other wise its normal. my purpose is count how many task is pending. so i can use function colorcount() as: 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 and in cell D1 I can use fuction =countcolor(A2:A20,A1) A1 = BACKGROUND COLOR WHICH WE WANT COUNT ITS WORKING FINE BUT PROBLEM IS WHEN CHANGE BACKGROUND COLOR IN CELL, D1 VALUE IS NOT CHANGE AUTOMATICALLY. ITS CHANGE WHEN I SELECT D1 CELL, PRESS F2 AND ENTER. PLEASE TELL ME WHAT I CAN DO SO VALUE CHANGE AUTOMATICALLY THANKS IN ADVANCE RKS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refresh - Value not refresh (URGENT)
Try adding
Application.Volatile after the Dims. Function CountColor(Rng As Range, RngColor As Range) As Integer Dim Cll As Range Dim Clr As Long Application.Volatile Clr = RngColor.Range("A1").Interior.Color For Each Cll In Rng If Cll.Interior.Color = Clr Then CountColor = CountColor + 1 Next Cll End Function -- Jim "RKS" wrote in message ... | Hi, | I am counting the cell which background color is red. and each cell i m | useing conditional formate if value less than target it means cell background | color is red other wise its normal. my purpose is count how many task is | pending. so i can use function colorcount() as: | | 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 | | and in cell D1 I can use fuction =countcolor(A2:A20,A1) | A1 = BACKGROUND COLOR WHICH WE WANT COUNT | | ITS WORKING FINE BUT PROBLEM IS WHEN CHANGE BACKGROUND COLOR IN CELL, D1 | VALUE IS NOT CHANGE AUTOMATICALLY. ITS CHANGE WHEN I SELECT D1 CELL, PRESS F2 | AND ENTER. | | PLEASE TELL ME WHAT I CAN DO SO VALUE CHANGE AUTOMATICALLY | THANKS IN ADVANCE | RKS | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refresh - Value not refresh (URGENT)
Thanks Jim for reply
Its not working. when i change cell background color its not change automatially. tell me one thing. i am useing conditional formate and there useing pattern color. pattern color or back ground color are same. if not please change my function. because we are using conditional when value is less or equal cell patern color is red. please reply me "Jim Rech" wrote: Try adding Application.Volatile after the Dims. Function CountColor(Rng As Range, RngColor As Range) As Integer Dim Cll As Range Dim Clr As Long Application.Volatile Clr = RngColor.Range("A1").Interior.Color For Each Cll In Rng If Cll.Interior.Color = Clr Then CountColor = CountColor + 1 Next Cll End Function -- Jim "RKS" wrote in message ... | Hi, | I am counting the cell which background color is red. and each cell i m | useing conditional formate if value less than target it means cell background | color is red other wise its normal. my purpose is count how many task is | pending. so i can use function colorcount() as: | | 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 | | and in cell D1 I can use fuction =countcolor(A2:A20,A1) | A1 = BACKGROUND COLOR WHICH WE WANT COUNT | | ITS WORKING FINE BUT PROBLEM IS WHEN CHANGE BACKGROUND COLOR IN CELL, D1 | VALUE IS NOT CHANGE AUTOMATICALLY. ITS CHANGE WHEN I SELECT D1 CELL, PRESS F2 | AND ENTER. | | PLEASE TELL ME WHAT I CAN DO SO VALUE CHANGE AUTOMATICALLY | THANKS IN ADVANCE | RKS | | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refresh - Value not refresh (URGENT)
Its not working. when i change cell background color its not change
automatially. You must make the sheet calculate. Excel only calcs on its own if a cell entry changes. Merely changing a cell color is not enough. Also, I do not think a macro can get the interior color when it is set by conditional formatting. You said your formula was working when you pressed F2 and Enter. I was surprised by that but I accepted it and worried only about making your function volatile. -- Jim "RKS" wrote in message ... | Thanks Jim for reply | | Its not working. when i change cell background color its not change | automatially. | tell me one thing. i am useing conditional formate and there useing pattern | color. pattern color or back ground color are same. if not please change my | function. because we are using conditional when value is less or equal cell | patern color is red. | please reply me | | | "Jim Rech" wrote: | | Try adding | | Application.Volatile | | after the Dims. | | Function CountColor(Rng As Range, RngColor As Range) As Integer | Dim Cll As Range | Dim Clr As Long | Application.Volatile | Clr = RngColor.Range("A1").Interior.Color | For Each Cll In Rng | If Cll.Interior.Color = Clr Then CountColor = CountColor + 1 | Next Cll | End Function | | -- | Jim | "RKS" wrote in message | ... | | Hi, | | I am counting the cell which background color is red. and each cell i m | | useing conditional formate if value less than target it means cell | background | | color is red other wise its normal. my purpose is count how many task is | | pending. so i can use function colorcount() as: | | | | 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 | | | | and in cell D1 I can use fuction =countcolor(A2:A20,A1) | | A1 = BACKGROUND COLOR WHICH WE WANT COUNT | | | | ITS WORKING FINE BUT PROBLEM IS WHEN CHANGE BACKGROUND COLOR IN CELL, D1 | | VALUE IS NOT CHANGE AUTOMATICALLY. ITS CHANGE WHEN I SELECT D1 CELL, PRESS | F2 | | AND ENTER. | | | | PLEASE TELL ME WHAT I CAN DO SO VALUE CHANGE AUTOMATICALLY | | THANKS IN ADVANCE | | RKS | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
URGENT! Retain format of cells in pivot table upon refresh | Excel Discussion (Misc queries) | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
How can I 'Enable Automatic Refresh' for Query Refresh by default | Setting up and Configuration of Excel | |||
Query Refresh-Enable Automatic Refresh Dialogue Box | Excel Discussion (Misc queries) |