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

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
|
|

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
|
|

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
| |
| |
|
|
|