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