Chip Pearson UDF returns #VALUE! error please help, just cannotfigure out why?

[Mark Stephens]

Hi there,

Really hope someone can help me here as I am simply stuck!

I am attempting to use Chip Pearson's code here: http://www.cpearson.com/Excel/CFColors.htm

to detect the conditionally formatted color fo a cell (or to be more accurante a range of cells). I pasted all Chips code into my macro enabked wrkbook and typed in the formula and it simply does not work. I know Chip wouldn't post dysfunctional code on his site so it must be something wrong with myimplementation but I am flummoxed by whay exactly, hope someone can suggest something.

To troubleshoot/debug the code I inserted a break in the code that is run when the udf is calculating and I have found the source of the problem. The code for the functionn i am using calls a sub function which runs fine until the last line here:

Function ActiveCondition(Rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
Dim Temp As Variant
Dim Temp2 As Variant

If Rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For Ndx = 1 To Rng.FormatConditions.Count

Set FC = Rng.FormatConditions(Ndx)

It is counting the number of conditions in the range (1) so i cannot see why it is not able to set the condition. Waht happens at this point is that the code simply stops running, no error essage, nothing it just stops, whichis rather unhelpful and leaves me with no idea what to do now.

Hope someone can help as I am desperate to get it working.

Thanks in advance, Mark

 

[Howard]

Hi there,



Really hope someone can help me here as I am simply stuck!



I am attempting to use Chip Pearson's code here: http://www.cpearson.com/Excel/CFColors.htm



to detect the conditionally formatted color fo a cell (or to be more accurante a range of cells). I pasted all Chips code into my macro enabked wrkbook and typed in the formula and it simply does not work. I know Chip wouldn't post dysfunctional code on his site so it must be something wrong with my implementation but I am flummoxed by whay exactly, hope someone can suggest something.



To troubleshoot/debug the code I inserted a break in the code that is runwhen the udf is calculating and I have found the source of the problem. The code for the functionn i am using calls a sub function which runs fine until the last line here:



Function ActiveCondition(Rng As Range) As Integer

Dim Ndx As Long

Dim FC As FormatCondition

Dim Temp As Variant

Dim Temp2 As Variant



If Rng.FormatConditions.Count = 0 Then

ActiveCondition = 0

Else

For Ndx = 1 To Rng.FormatConditions.Count



Set FC = Rng.FormatConditions(Ndx)



It is counting the number of conditions in the range (1) so i cannot see why it is not able to set the condition. Waht happens at this point is thatthe code simply stops running, no error essage, nothing it just stops, which is rather unhelpful and leaves me with no idea what to do now.



Hope someone can help as I am desperate to get it working.



Thanks in advance, Mark

The very first line on that site of Chip's reads...

Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell.

This is my guess why the code does not work for you.

Regards,
Howard

 

[Mark Stephens]

The very first line on that site of Chip's reads...



Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell.



This is my guess why the code does not work for you.



Regards,

Howard

Hi Howard, thanks for the suggestion however the very next line reads:

In order to determine these, you need code that will test the format conditions. This page describes several VBA functions that will do this for you

So that isn't it, any other ideas?

Kind regards, Mark

 

[Howard]

Hi Howard, thanks for the suggestion however the very next line reads:



In order to determine these, you need code that will test the format conditions. This page describes several VBA functions that will do this for you



So that isn't it, any other ideas?



Kind regards, Mark

Hmmmm...so it does. If you have tried all the stuff Chip offers, I'm at a loss.
Do you read that second sentence as "a means to TEST for CF and/or the conditions" or that Chip is offering work-arounds that mirror normal color formatting solutions?

I,ve had it im mind CF just don't respond as other color formatting to sorting or counting or suming values in a certain range of colors and the like.I often see MVP suggesting to use the formula that PRODUCES the CF to solve their queries.

Good luck.
Howard

 

[Mark Stephens]

Hmmmm...so it does. If you have tried all the stuff Chip offers, I'm at a loss.

Do you read that second sentence as "a means to TEST for CF and/or the conditions" or that Chip is offering work-arounds that mirror normal color formatting solutions?



I,ve had it im mind CF just don't respond as other color formatting to sorting or counting or suming values in a certain range of colors and the like. I often see MVP suggesting to use the formula that PRODUCES the CF to solve their queries.



Good luck.

Howard

Hi Howard,

Yes I do think he is offering a real solution, and I think it has to work iam just missing something but no idea what. There are lots of other posts from guys who assert that their code solves the issue but i have identifiedthe problem which is that the object model does not recognise the formatcondition by index number and all these solutins use that, here's the code which works right up to the last line when it just stops running, which i find so strange as obviously the .count function works perfectly, so if it cancount the number of conditions how come it cannot call one by index numberit just makes no sense, anyway see below for the code example and i will let u know when i solve it which i will do it if it is my last act on this earth!!!

Public Function ColorMFC(rg As Range, Optional Mode As Byte = 0) As Variant
Dim e As Long, i As Byte, LoTest As Boolean
Dim LoMFC As FormatCondition
Application.Volatile
'loop depending on condition(s)
'if there is no MFC .FormatConditions.Count return 0
For i = 1 To rg.FormatConditions.Count
Set LoMFC = rg.FormatConditions(i)