How do I determine if conditional formatting is applied to a spreadsheet cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.

Start with this kind of logic. If the count is 0 then no CF

Sub CondCount()
fcnt = Selection.FormatConditions.Count
MsgBox fcnt
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MarkTheNuke" wrote in message
...
How do I determine if conditional formatting is applied to a spreadsheet
cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.

This tells if there is a condition, but unless I read Mark's original post
wrong I think he wants to know if the format is applied; i.e. active. That
turns out to be a difficult issue! There is no property that tells you
quickly if a format condition is met or no, at least none I know of. The
best we have is the formula in FormatConditions(n).Formula1. But that is a
string and would need to be processed to turn it into VBA code to evaluate -
yikes. So then I thought, why not temporarily put the formula from the
conditional formatting into the cell formula and use the worksheet
calculations to test the result (I would store the original cell formula and
then paste it back in the cell when done). I found first that the formula
text when read is applied to the ACTIVE cell, even though you are reading the
property for a specified cell - so relative references will be a problem
unless you first activate the cell you want to test. I could get the test to
work, but ran into one problem: if the conditional format is self-referential
(i.e. if it looks at the cell it is being applied to) I end up with a
circular reference when I replace the cell formula with the conditional
formula. I don't know any easy solution to this - the best I can think is to
parse that conditional formula but that would be an incredibly difficult
task...

Anyone know any way around this?

"Ken Wright" wrote:

Start with this kind of logic. If the count is 0 then no CF

Sub CondCount()
fcnt = Selection.FormatConditions.Count
MsgBox fcnt
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MarkTheNuke" wrote in message
...
How do I determine if conditional formatting is applied to a spreadsheet
cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.

Chip Pearson shows a way:

http://cpearson.com/excel/CFColors.htm

K Dales wrote:

This tells if there is a condition, but unless I read Mark's original post
wrong I think he wants to know if the format is applied; i.e. active. That
turns out to be a difficult issue! There is no property that tells you
quickly if a format condition is met or no, at least none I know of. The
best we have is the formula in FormatConditions(n).Formula1. But that is a
string and would need to be processed to turn it into VBA code to evaluate -
yikes. So then I thought, why not temporarily put the formula from the
conditional formatting into the cell formula and use the worksheet
calculations to test the result (I would store the original cell formula and
then paste it back in the cell when done). I found first that the formula
text when read is applied to the ACTIVE cell, even though you are reading the
property for a specified cell - so relative references will be a problem
unless you first activate the cell you want to test. I could get the test to
work, but ran into one problem: if the conditional format is self-referential
(i.e. if it looks at the cell it is being applied to) I end up with a
circular reference when I replace the cell formula with the conditional
formula. I don't know any easy solution to this - the best I can think is to
parse that conditional formula but that would be an incredibly difficult
task...

Anyone know any way around this?

"Ken Wright" wrote:

Start with this kind of logic. If the count is 0 then no CF

Sub CondCount()
fcnt = Selection.FormatConditions.Count
MsgBox fcnt
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MarkTheNuke" wrote in message
...
How do I determine if conditional formatting is applied to a spreadsheet
cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.




--

Dave Peterson

Thanks - helpful, but still has a limitation (conditions must use absolute
cell references). Still would like to find a way that does not place any
limitation on the type of condition allowed.

"Dave Peterson" wrote:

Chip Pearson shows a way:

http://cpearson.com/excel/CFColors.htm

K Dales wrote:

This tells if there is a condition, but unless I read Mark's original post
wrong I think he wants to know if the format is applied; i.e. active. That
turns out to be a difficult issue! There is no property that tells you
quickly if a format condition is met or no, at least none I know of. The
best we have is the formula in FormatConditions(n).Formula1. But that is a
string and would need to be processed to turn it into VBA code to evaluate -
yikes. So then I thought, why not temporarily put the formula from the
conditional formatting into the cell formula and use the worksheet
calculations to test the result (I would store the original cell formula and
then paste it back in the cell when done). I found first that the formula
text when read is applied to the ACTIVE cell, even though you are reading the
property for a specified cell - so relative references will be a problem
unless you first activate the cell you want to test. I could get the test to
work, but ran into one problem: if the conditional format is self-referential
(i.e. if it looks at the cell it is being applied to) I end up with a
circular reference when I replace the cell formula with the conditional
formula. I don't know any easy solution to this - the best I can think is to
parse that conditional formula but that would be an incredibly difficult
task...

Anyone know any way around this?

"Ken Wright" wrote:

Start with this kind of logic. If the count is 0 then no CF

Sub CondCount()
fcnt = Selection.FormatConditions.Count
MsgBox fcnt
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MarkTheNuke" wrote in message
...
How do I determine if conditional formatting is applied to a spreadsheet
cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.




--

Dave Peterson

There is another problem with the solution at
http://cpearson.com/excel/CFColors.htm
There is an undefined function in the demo code.
GetStrippedValue(cellReference as Range)
Makes it really hard to try out a 'solution' if something is missing. Plus
it looks like the GetStrippedValue might be a formidable function.

"K Dales" wrote:

Thanks - helpful, but still has a limitation (conditions must use absolute
cell references). Still would like to find a way that does not place any
limitation on the type of condition allowed.

"Dave Peterson" wrote:

Chip Pearson shows a way:

http://cpearson.com/excel/CFColors.htm

K Dales wrote:

This tells if there is a condition, but unless I read Mark's original post
wrong I think he wants to know if the format is applied; i.e. active. That
turns out to be a difficult issue! There is no property that tells you
quickly if a format condition is met or no, at least none I know of. The
best we have is the formula in FormatConditions(n).Formula1. But that is a
string and would need to be processed to turn it into VBA code to evaluate -
yikes. So then I thought, why not temporarily put the formula from the
conditional formatting into the cell formula and use the worksheet
calculations to test the result (I would store the original cell formula and
then paste it back in the cell when done). I found first that the formula
text when read is applied to the ACTIVE cell, even though you are reading the
property for a specified cell - so relative references will be a problem
unless you first activate the cell you want to test. I could get the test to
work, but ran into one problem: if the conditional format is self-referential
(i.e. if it looks at the cell it is being applied to) I end up with a
circular reference when I replace the cell formula with the conditional
formula. I don't know any easy solution to this - the best I can think is to
parse that conditional formula but that would be an incredibly difficult
task...

Anyone know any way around this?

"Ken Wright" wrote:

Start with this kind of logic. If the count is 0 then no CF

Sub CondCount()
fcnt = Selection.FormatConditions.Count
MsgBox fcnt
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MarkTheNuke" wrote in message
...
How do I determine if conditional formatting is applied to a spreadsheet
cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.




--

Dave Peterson