Solved: need help with #value! error

[Goldi]

All:
I have a spreadsheet that turns the cell background red based on conditional formatting. I want to count the cells that have turned red. I am trying to use a user defined fuction to count the cells. The VBA code I am using came from Chip Pearon's web site. I copied the VBA code from Chips's website into a module and added the user defined fuction. All I get is a #value error. Can anyone tell me what is wrong?
I have attached the Excel I am working on.
Goldi
5198

[mdmackillop]

Hi Goldi,
Welcome to VBAX
The function etc. all seem OK and I'm not sure of the reason for the problem. However, changing the value in C10 and then restoring it seems to get the function "working". The result doesn't look right, so if you still have problems, let us know.

[Goldi]

I changed to value in C10 and I got a count to show up in C8, but the count still isn't right. What do you suggest?
Goldi

[Goldi]

If I change the value in C10 to 101, C10 turns white and then I get a count of 0 in cell c8.
If I change the value in C10 to 95, c10 turns red and then I get a count of 14 in cell c8.
I think that I have misunderstood what Chip's code is supposed to do. Is there another way that I can count the red cells in column C? I tried countif and couldn't figure out how to make it an array.
Goldi

[mdmackillop]

Here's your example with some debug code added. It seems to evaluate only the CF in the active cell. Not clear why yet.

[mdmackillop]

Hi Goldi,
You missed the last line here

NOTE: ActiveCondition may result in an inaccurate result if the following are true:
You are calling ActiveCondtion from a worksheet cell, AND
The cell passed to ActiveCondtion uses a "Formula Is" rather than
"Cell Value Is" condition, AND
The formula used in the condition formula contains relative addresses
Change your CF all to absolute references and the code should work.

[Goldi]

After I changed all the CF to absolute references I got the correct result. Thank you very much for your help.
Goldi:wiggle: