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

Hi there,

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

I am attempting to use Chip Pearson's code he 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 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 he

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

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

On Sunday, December 23, 2012 10:25:02 PM UTC-8, Mark Stephens wrote:
Hi there,



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



I am attempting to use Chip Pearson's code he 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 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 he



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

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

On Monday, 24 December 2012 06:56:11 UTC, Howard wrote:
On Sunday, December 23, 2012 10:25:02 PM UTC-8, Mark Stephens wrote:

Hi there,







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







I am attempting to use Chip Pearson's code he 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 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 he







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

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

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

On Sunday, December 23, 2012 11:10:06 PM UTC-8, Mark Stephens wrote:
On Monday, 24 December 2012 06:56:11 UTC, Howard wrote:

On Sunday, December 23, 2012 10:25:02 PM UTC-8, Mark Stephens wrote:



Hi there,















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















I am attempting to use Chip Pearson's code he 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 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 he















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



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

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

On Monday, December 24, 2012 8:04:35 AM UTC, Howard wrote:
On Sunday, December 23, 2012 11:10:06 PM UTC-8, Mark Stephens wrote:

On Monday, 24 December 2012 06:56:11 UTC, Howard wrote:



On Sunday, December 23, 2012 10:25:02 PM UTC-8, Mark Stephens wrote:







Hi there,































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































I am attempting to use Chip Pearson's code he 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 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 he































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







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

Hi Howard,

Yes I do think he is offering a real solution, and I think it has to work i am 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 identified the 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 can count the number of conditions how come it cannot call one by index number it 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)