How do I determine if conditional formatting is applied to an exc.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 an undefined function in the demo code.
> GetStrippedValue(cellReference as Range)
> Makes it really hard to try out a 'solution' if something is missing.

Makes it really hard to give a solution if people don't read all the text on
the page, especially the opening paragraph :-)
ActiveCondition
This function will return the number of the condition that is currently
applied to the cell. If the cell does not have any conditional formatting
defined, or none of the conditional formats are currently applied, it
returns 0. Otherwise, it returns 1, 2, or 3, indicating with format
condition is in effect.

>>>ActiveCondition requires the GetStrippedValue function at the bottom of
this page<<<


--
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > > > > news:[email protected]...
> > > > > > 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
> > >

Ok, you caught me there :@ I was looking around and I think I might have
found a cleaner solution using the Evaluate Method. However, it still does
not answer my question about determined if Conditional Formatting is applied,
I guess the answer is you can't, except by visual examination.
Mark

"Ken Wright" wrote:

> > 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.
>
> Makes it really hard to give a solution if people don't read all the text on
> the page, especially the opening paragraph :-)
> ActiveCondition
> This function will return the number of the condition that is currently
> applied to the cell. If the cell does not have any conditional formatting
> defined, or none of the conditional formats are currently applied, it
> returns 0. Otherwise, it returns 1, 2, or 3, indicating with format
> condition is in effect.
>
> >>>ActiveCondition requires the GetStrippedValue function at the bottom of
> this page<<<
>
>
> --
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > >
>
>
>

LOL :-)

--
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" <[email protected]> wrote in message
news:[email protected]...
> Ok, you caught me there :@ I was looking around and I think I might have
> found a cleaner solution using the Evaluate Method. However, it still
does
> not answer my question about determined if Conditional Formatting is
applied,
> I guess the answer is you can't, except by visual examination.
> Mark
<snip>

Well it looks like there is no way of programatically determining if
conditional formatting is applied, the best to be hoped for is determining if
conditional formatting should be applied based on the contents of the cell
and the conditional formatting conditions. I have it all in a spreadsheet, I
will provide a link once I get it onto my web pages. (In about 2 years)


"Ken Wright" wrote:

> LOL :-)
>
> --
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Ok, you caught me there :@ I was looking around and I think I might have
> > found a cleaner solution using the Evaluate Method. However, it still
> does
> > not answer my question about determined if Conditional Formatting is
> applied,
> > I guess the answer is you can't, except by visual examination.
> > Mark
> <snip>
>
>
>

Did you look at Chip Pearson's site (previously mentioned in this thread)?

Chip Pearson shows a way:
http://cpearson.com/excel/CFColors.htm




MarkTheNuke wrote:
>
> Well it looks like there is no way of programatically determining if
> conditional formatting is applied, the best to be hoped for is determining if
> conditional formatting should be applied based on the contents of the cell
> and the conditional formatting conditions. I have it all in a spreadsheet, I
> will provide a link once I get it onto my web pages. (In about 2 years)
>
> "Ken Wright" wrote:
>
> > LOL :-)
> >
> > --
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > Ok, you caught me there :@ I was looking around and I think I might have
> > > found a cleaner solution using the Evaluate Method. However, it still
> > does
> > > not answer my question about determined if Conditional Formatting is
> > applied,
> > > I guess the answer is you can't, except by visual examination.
> > > Mark
> > <snip>
> >
> >
> >

--

Dave Peterson

Yes, I did look at the Chip Pearson site. I have finished my VBA code for
determining if ConditionalFormatting is applied. You can view the results at:
http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any
questions or comments you can post them to this message.

"Dave Peterson" wrote:

> Did you look at Chip Pearson's site (previously mentioned in this thread)?
>
> Chip Pearson shows a way:
> http://cpearson.com/excel/CFColors.htm
>
>
>
>
> MarkTheNuke wrote:
> >
> > Well it looks like there is no way of programatically determining if
> > conditional formatting is applied, the best to be hoped for is determining if
> > conditional formatting should be applied based on the contents of the cell
> > and the conditional formatting conditions. I have it all in a spreadsheet, I
> > will provide a link once I get it onto my web pages. (In about 2 years)
> >
> > "Ken Wright" wrote:
> >
> > > LOL :-)
> > >
> > > --
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Ok, you caught me there :@ I was looking around and I think I might have
> > > > found a cleaner solution using the Evaluate Method. However, it still
> > > does
> > > > not answer my question about determined if Conditional Formatting is
> > > applied,
> > > > I guess the answer is you can't, except by visual examination.
> > > > Mark
> > > <snip>
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

A few people have tried to point out to you why you were not succeeding with
Chip's function. And that despite the clear instructions given by Chip. I'll
have a go as well because, having just read your link to your page, I was
amazed to see your comment:

"I was suprised that the author [Chip] did not use the Evaluate Method"

I've seen various functions relating to Format Conditions, and written one
of my own (some different features to anything I've seen). They all use
Evaluate, including of course Chip's.

Could I suggest as others have that you carefully read Chip's page in full,
together with previous advice given in this thread. Re-read repeatedly until
the light dawns. It will :-)

Regards,
Peter T

"MarkTheNuke" <[email protected]> wrote in message
news:[email protected]...
> Yes, I did look at the Chip Pearson site. I have finished my VBA code for
> determining if ConditionalFormatting is applied. You can view the results
at:
> http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have
any
> questions or comments you can post them to this message.
>
> "Dave Peterson" wrote:
>
> > Did you look at Chip Pearson's site (previously mentioned in this
thread)?
> >
> > Chip Pearson shows a way:
> > http://cpearson.com/excel/CFColors.htm
> >
> >
> >
> >
> > MarkTheNuke wrote:
> > >
> > > Well it looks like there is no way of programatically determining if
> > > conditional formatting is applied, the best to be hoped for is
determining if
> > > conditional formatting should be applied based on the contents of the
cell
> > > and the conditional formatting conditions. I have it all in a
spreadsheet, I
> > > will provide a link once I get it onto my web pages. (In about 2
years)
> > >
> > > "Ken Wright" wrote:
> > >
> > > > LOL :-)
> > > >
> > > > --
> > > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > Ok, you caught me there :@ I was looking around and I think I
might have
> > > > > found a cleaner solution using the Evaluate Method. However, it
still
> > > > does
> > > > > not answer my question about determined if Conditional Formatting
is
> > > > applied,
> > > > > I guess the answer is you can't, except by visual examination.
> > > > > Mark
> > > > <snip>
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

Yes, Chip does use Evaluate for the expression type of format conditions,
however, if you play around with conditional formatting, you can enter a
formula into the value comparison routines, which means you would have to use
the Evaluate Method for the value comparison routine. Like I said, I was
looking for a specific result, which is why I wrote my own spreadsheet. I
did not mean to disparage Chips effort, if I did then I apologize. I just
made a comment about something I thought was missing.
I did read Chips routines, more than once, and like I said the big thing I
was looking for was whether ConditionalFormatting should have been applied,
actually I was looking if it was applied, but there is no function that
determines that, maybe you misread my original question, or I was not clear
enough.

"Peter T" wrote:

> A few people have tried to point out to you why you were not succeeding with
> Chip's function. And that despite the clear instructions given by Chip. I'll
> have a go as well because, having just read your link to your page, I was
> amazed to see your comment:
>
> "I was suprised that the author [Chip] did not use the Evaluate Method"
>
> I've seen various functions relating to Format Conditions, and written one
> of my own (some different features to anything I've seen). They all use
> Evaluate, including of course Chip's.
>
> Could I suggest as others have that you carefully read Chip's page in full,
> together with previous advice given in this thread. Re-read repeatedly until
> the light dawns. It will :-)
>
> Regards,
> Peter T
>
> "MarkTheNuke" <[email protected]> wrote in message
> news:[email protected]...
> > Yes, I did look at the Chip Pearson site. I have finished my VBA code for
> > determining if ConditionalFormatting is applied. You can view the results
> at:
> > http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have
> any
> > questions or comments you can post them to this message.
> >
> > "Dave Peterson" wrote:
> >
> > > Did you look at Chip Pearson's site (previously mentioned in this
> thread)?
> > >
> > > Chip Pearson shows a way:
> > > http://cpearson.com/excel/CFColors.htm
> > >
> > >
> > >
> > >
> > > MarkTheNuke wrote:
> > > >
> > > > Well it looks like there is no way of programatically determining if
> > > > conditional formatting is applied, the best to be hoped for is
> determining if
> > > > conditional formatting should be applied based on the contents of the
> cell
> > > > and the conditional formatting conditions. I have it all in a
> spreadsheet, I
> > > > will provide a link once I get it onto my web pages. (In about 2
> years)
> > > >
> > > > "Ken Wright" wrote:
> > > >
> > > > > LOL :-)
> > > > >
> > > > > --
> > > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > Ok, you caught me there :@ I was looking around and I think I
> might have
> > > > > > found a cleaner solution using the Evaluate Method. However, it
> still
> > > > > does
> > > > > > not answer my question about determined if Conditional Formatting
> is
> > > > > applied,
> > > > > > I guess the answer is you can't, except by visual examination.
> > > > > > Mark
> > > > > <snip>
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>

Comments in line:

> maybe you misread my original question, or I was not clear

Could be either / or. Let's find out -

> and like I said the big thing I was looking for was whether
> ConditionalFormatting should have been applied

Assuming you are looking at single cell A1 you would enter
=ActiveCondition(A1)

This will return 0 (no conditions are true) or 1 to 3 to indicate which of
the three conditions are true. Doesn't that fullfill the requirement?

> actually I was looking if it was applied,

Not quite sure what you mean here unless you want to return the ColorIndex
of Fill or Font that been applied relates to a true condition, or the
underflying index if no conditions are true (btw - a function very
adaptable to own needs)
=ColorIndexOfCF(A1,false)

Have you put all Chip's functions, including the required helper
GetStrippedValue, into a module and tried each.

What have I missed?

Regards,
Peter T



"MarkTheNuke" <[email protected]> wrote in message
news:[email protected]...
> Yes, Chip does use Evaluate for the expression type of format conditions,
> however, if you play around with conditional formatting, you can enter a
> formula into the value comparison routines, which means you would have to
use
> the Evaluate Method for the value comparison routine. Like I said, I was
> looking for a specific result, which is why I wrote my own spreadsheet. I
> did not mean to disparage Chips effort, if I did then I apologize. I just
> made a comment about something I thought was missing.
> I did read Chips routines, more than once, and like I said the big thing I
> was looking for was whether ConditionalFormatting should have been
applied,
> actually I was looking if it was applied, but there is no function that
> determines that, maybe you misread my original question, or I was not
clear
> enough.
>
> "Peter T" wrote:
>
> > A few people have tried to point out to you why you were not succeeding
with
> > Chip's function. And that despite the clear instructions given by Chip.
I'll
> > have a go as well because, having just read your link to your page, I
was
> > amazed to see your comment:
> >
> > "I was suprised that the author [Chip] did not use the Evaluate Method"
> >
> > I've seen various functions relating to Format Conditions, and written
one
> > of my own (some different features to anything I've seen). They all use
> > Evaluate, including of course Chip's.
> >
> > Could I suggest as others have that you carefully read Chip's page in
full,
> > together with previous advice given in this thread. Re-read repeatedly
until
> > the light dawns. It will :-)
> >
> > Regards,
> > Peter T
> >
> > "MarkTheNuke" <[email protected]> wrote in message
> > news:[email protected]...
> > > Yes, I did look at the Chip Pearson site. I have finished my VBA code
for
> > > determining if ConditionalFormatting is applied. You can view the
results
> > at:
> > > http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you
have
> > any
> > > questions or comments you can post them to this message.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Did you look at Chip Pearson's site (previously mentioned in this
> > thread)?
> > > >
> > > > Chip Pearson shows a way:
> > > > http://cpearson.com/excel/CFColors.htm
> > > >
> > > >
> > > >
> > > >
> > > > MarkTheNuke wrote:
> > > > >
> > > > > Well it looks like there is no way of programatically determining
if
> > > > > conditional formatting is applied, the best to be hoped for is
> > determining if
> > > > > conditional formatting should be applied based on the contents of
the
> > cell
> > > > > and the conditional formatting conditions. I have it all in a
> > spreadsheet, I
> > > > > will provide a link once I get it onto my web pages. (In about 2
> > years)
> > > > >
> > > > > "Ken Wright" wrote:
> > > > >
> > > > > > LOL :-)
> > > > > >
> > > > > > --
> > > > > > 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" <[email protected]> wrote in
> > message
> > > > > > news:[email protected]...
> > > > > > > Ok, you caught me there :@ I was looking around and I think I
> > might have
> > > > > > > found a cleaner solution using the Evaluate Method. However,
it
> > still
> > > > > > does
> > > > > > > not answer my question about determined if Conditional
Formatting
> > is
> > > > > > applied,
> > > > > > > I guess the answer is you can't, except by visual examination.
> > > > > > > Mark
> > > > > > <snip>
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> >
> >

Perhaps I did misread, quoted from from your previous post:

>you can enter a formula into the value comparison routines, which means you
>would have to use the Evaluate Method for the value comparison routine

I've never thought to do that - enter a formula to be compared. Quite
unusual. In which case simple enough to amend Chip's functions. Along the
lines that if the first character in the string is an "=" it's probably a
formula that's best evaluated.

In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an
if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need
a bit more checking.

Regards,
Peter T.


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Comments in line:
>
> > maybe you misread my original question, or I was not clear
>
> Could be either / or. Let's find out -
>
> > and like I said the big thing I was looking for was whether
> > ConditionalFormatting should have been applied
>
> Assuming you are looking at single cell A1 you would enter
> =ActiveCondition(A1)
>
> This will return 0 (no conditions are true) or 1 to 3 to indicate which of
> the three conditions are true. Doesn't that fullfill the requirement?
>
> > actually I was looking if it was applied,
>
> Not quite sure what you mean here unless you want to return the ColorIndex
> of Fill or Font that been applied relates to a true condition, or the
> underflying index if no conditions are true (btw - a function very
> adaptable to own needs)
> =ColorIndexOfCF(A1,false)
>
> Have you put all Chip's functions, including the required helper
> GetStrippedValue, into a module and tried each.
>
> What have I missed?
>
> Regards,
> Peter T
>
>
>
> "MarkTheNuke" <[email protected]> wrote in message
> news:[email protected]...
> > Yes, Chip does use Evaluate for the expression type of format
conditions,
> > however, if you play around with conditional formatting, you can enter a
> > formula into the value comparison routines, which means you would have
to
> use
> > the Evaluate Method for the value comparison routine. Like I said, I
was
> > looking for a specific result, which is why I wrote my own spreadsheet.
I
> > did not mean to disparage Chips effort, if I did then I apologize. I
just
> > made a comment about something I thought was missing.
> > I did read Chips routines, more than once, and like I said the big thing
I
> > was looking for was whether ConditionalFormatting should have been
> applied,
> > actually I was looking if it was applied, but there is no function that
> > determines that, maybe you misread my original question, or I was not
> clear
> > enough.
> >
> > "Peter T" wrote:
> >
> > > A few people have tried to point out to you why you were not
succeeding
> with
> > > Chip's function. And that despite the clear instructions given by
Chip.
> I'll
> > > have a go as well because, having just read your link to your page, I
> was
> > > amazed to see your comment:
> > >
> > > "I was suprised that the author [Chip] did not use the Evaluate
Method"
> > >
> > > I've seen various functions relating to Format Conditions, and written
> one
> > > of my own (some different features to anything I've seen). They all
use
> > > Evaluate, including of course Chip's.
> > >
> > > Could I suggest as others have that you carefully read Chip's page in
> full,
> > > together with previous advice given in this thread. Re-read repeatedly
> until
> > > the light dawns. It will :-)
> > >
> > > Regards,
> > > Peter T
> > >
> > > "MarkTheNuke" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Yes, I did look at the Chip Pearson site. I have finished my VBA
code
> for
> > > > determining if ConditionalFormatting is applied. You can view the
> results
> > > at:
> > > > http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you
> have
> > > any
> > > > questions or comments you can post them to this message.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Did you look at Chip Pearson's site (previously mentioned in this
> > > thread)?
> > > > >
> > > > > Chip Pearson shows a way:
> > > > > http://cpearson.com/excel/CFColors.htm
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > MarkTheNuke wrote:
> > > > > >
> > > > > > Well it looks like there is no way of programatically
determining
> if
> > > > > > conditional formatting is applied, the best to be hoped for is
> > > determining if
> > > > > > conditional formatting should be applied based on the contents
of
> the
> > > cell
> > > > > > and the conditional formatting conditions. I have it all in a
> > > spreadsheet, I
> > > > > > will provide a link once I get it onto my web pages. (In about
2
> > > years)
> > > > > >
> > > > > > "Ken Wright" wrote:
> > > > > >
> > > > > > > LOL :-)
> > > > > > >
> > > > > > > --
> > > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > news:[email protected]...
> > > > > > > > Ok, you caught me there :@ I was looking around and I think
I
> > > might have
> > > > > > > > found a cleaner solution using the Evaluate Method.
However,
> it
> > > still
> > > > > > > does
> > > > > > > > not answer my question about determined if Conditional
> Formatting
> > > is
> > > > > > > applied,
> > > > > > > > I guess the answer is you can't, except by visual
examination.
> > > > > > > > Mark
> > > > > > > <snip>
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > >
> > >
>
>

You don't even have to enter a formula, if you enter a cell reference it will
fail because it performs a string comparision on the cell address instead of
getting the value of the referenced cell. If you use the Evaluate Method,
you have to use the worksheet object as the calling reference, since if you
do use cell addresses they are not resolved to their correct worksheet. You
can't enter Sheet2!$A$1 as a conditional value, but you can enter $A$1.
Like I said, a lot of what I did was refactor the code that Chip provided.
Also I did paste Chip's code into an excel spreadsheet, unfortunately it did
fail when I used my first address.

"Peter T" wrote:

> Perhaps I did misread, quoted from from your previous post:
>
> >you can enter a formula into the value comparison routines, which means you
> >would have to use the Evaluate Method for the value comparison routine
>
> I've never thought to do that - enter a formula to be compared. Quite
> unusual. In which case simple enough to amend Chip's functions. Along the
> lines that if the first character in the string is an "=" it's probably a
> formula that's best evaluated.
>
> In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an
> if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need
> a bit more checking.
>
> Regards,
> Peter T.
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > Comments in line:
> >
> > > maybe you misread my original question, or I was not clear
> >
> > Could be either / or. Let's find out -
> >
> > > and like I said the big thing I was looking for was whether
> > > ConditionalFormatting should have been applied
> >
> > Assuming you are looking at single cell A1 you would enter
> > =ActiveCondition(A1)
> >
> > This will return 0 (no conditions are true) or 1 to 3 to indicate which of
> > the three conditions are true. Doesn't that fullfill the requirement?
> >
> > > actually I was looking if it was applied,
> >
> > Not quite sure what you mean here unless you want to return the ColorIndex
> > of Fill or Font that been applied relates to a true condition, or the
> > underflying index if no conditions are true (btw - a function very
> > adaptable to own needs)
> > =ColorIndexOfCF(A1,false)
> >
> > Have you put all Chip's functions, including the required helper
> > GetStrippedValue, into a module and tried each.
> >
> > What have I missed?
> >
> > Regards,
> > Peter T
> >
> >
> >
> > "MarkTheNuke" <[email protected]> wrote in message
> > news:[email protected]...
> > > Yes, Chip does use Evaluate for the expression type of format
> conditions,
> > > however, if you play around with conditional formatting, you can enter a
> > > formula into the value comparison routines, which means you would have
> to
> > use
> > > the Evaluate Method for the value comparison routine. Like I said, I
> was
> > > looking for a specific result, which is why I wrote my own spreadsheet.
> I
> > > did not mean to disparage Chips effort, if I did then I apologize. I
> just
> > > made a comment about something I thought was missing.
> > > I did read Chips routines, more than once, and like I said the big thing
> I
> > > was looking for was whether ConditionalFormatting should have been
> > applied,
> > > actually I was looking if it was applied, but there is no function that
> > > determines that, maybe you misread my original question, or I was not
> > clear
> > > enough.
> > >
> > > "Peter T" wrote:
> > >
> > > > A few people have tried to point out to you why you were not
> succeeding
> > with
> > > > Chip's function. And that despite the clear instructions given by
> Chip.
> > I'll
> > > > have a go as well because, having just read your link to your page, I
> > was
> > > > amazed to see your comment:
> > > >
> > > > "I was suprised that the author [Chip] did not use the Evaluate
> Method"
> > > >
> > > > I've seen various functions relating to Format Conditions, and written
> > one
> > > > of my own (some different features to anything I've seen). They all
> use
> > > > Evaluate, including of course Chip's.
> > > >
> > > > Could I suggest as others have that you carefully read Chip's page in
> > full,
> > > > together with previous advice given in this thread. Re-read repeatedly
> > until
> > > > the light dawns. It will :-)
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "MarkTheNuke" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Yes, I did look at the Chip Pearson site. I have finished my VBA
> code
> > for
> > > > > determining if ConditionalFormatting is applied. You can view the
> > results
> > > > at:
> > > > > http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you
> > have
> > > > any
> > > > > questions or comments you can post them to this message.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Did you look at Chip Pearson's site (previously mentioned in this
> > > > thread)?
> > > > > >
> > > > > > Chip Pearson shows a way:
> > > > > > http://cpearson.com/excel/CFColors.htm
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > MarkTheNuke wrote:
> > > > > > >
> > > > > > > Well it looks like there is no way of programatically
> determining
> > if
> > > > > > > conditional formatting is applied, the best to be hoped for is
> > > > determining if
> > > > > > > conditional formatting should be applied based on the contents
> of
> > the
> > > > cell
> > > > > > > and the conditional formatting conditions. I have it all in a
> > > > spreadsheet, I
> > > > > > > will provide a link once I get it onto my web pages. (In about
> 2
> > > > years)
> > > > > > >
> > > > > > > "Ken Wright" wrote:
> > > > > > >
> > > > > > > > LOL :-)
> > > > > > > >
> > > > > > > > --
> > > > > > > > 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" <[email protected]> wrote in
> > > > message
> > > > > > > > news:[email protected]...
> > > > > > > > > Ok, you caught me there :@ I was looking around and I think
> I
> > > > might have
> > > > > > > > > found a cleaner solution using the Evaluate Method.
> However,
> > it
> > > > still
> > > > > > > > does
> > > > > > > > > not answer my question about determined if Conditional
> > Formatting
> > > > is
> > > > > > > > applied,
> > > > > > > > > I guess the answer is you can't, except by visual
> examination.
> > > > > > > > > Mark
> > > > > > > > <snip>
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

Perhaps I did misread, quoted from from your previous post:

>you can enter a formula into the value comparison routines, which means you
>would have to use the Evaluate Method for the value comparison routine

I've never thought to do that - enter a formula to be compared. Quite
unusual. In which case simple enough to amend Chip's functions. Along the
lines that if the first character in the string is an "=" it's probably a
formula that's best evaluated.

In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an
if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need
a bit more checking.

Regards,
Peter T.


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Comments in line:
>
> > maybe you misread my original question, or I was not clear
>
> Could be either / or. Let's find out -
>
> > and like I said the big thing I was looking for was whether
> > ConditionalFormatting should have been applied
>
> Assuming you are looking at single cell A1 you would enter
> =ActiveCondition(A1)
>
> This will return 0 (no conditions are true) or 1 to 3 to indicate which of
> the three conditions are true. Doesn't that fullfill the requirement?
>
> > actually I was looking if it was applied,
>
> Not quite sure what you mean here unless you want to return the ColorIndex
> of Fill or Font that been applied relates to a true condition, or the
> underflying index if no conditions are true (btw - a function very
> adaptable to own needs)
> =ColorIndexOfCF(A1,false)
>
> Have you put all Chip's functions, including the required helper
> GetStrippedValue, into a module and tried each.
>
> What have I missed?
>
> Regards,
> Peter T
>
>
>
> "MarkTheNuke" <[email protected]> wrote in message
> news:[email protected]...
> > Yes, Chip does use Evaluate for the expression type of format
conditions,
> > however, if you play around with conditional formatting, you can enter a
> > formula into the value comparison routines, which means you would have
to
> use
> > the Evaluate Method for the value comparison routine. Like I said, I
was
> > looking for a specific result, which is why I wrote my own spreadsheet.
I
> > did not mean to disparage Chips effort, if I did then I apologize. I
just
> > made a comment about something I thought was missing.
> > I did read Chips routines, more than once, and like I said the big thing
I
> > was looking for was whether ConditionalFormatting should have been
> applied,
> > actually I was looking if it was applied, but there is no function that
> > determines that, maybe you misread my original question, or I was not
> clear
> > enough.
> >
> > "Peter T" wrote:
> >
> > > A few people have tried to point out to you why you were not
succeeding
> with
> > > Chip's function. And that despite the clear instructions given by
Chip.
> I'll
> > > have a go as well because, having just read your link to your page, I
> was
> > > amazed to see your comment:
> > >
> > > "I was suprised that the author [Chip] did not use the Evaluate
Method"
> > >
> > > I've seen various functions relating to Format Conditions, and written
> one
> > > of my own (some different features to anything I've seen). They all
use
> > > Evaluate, including of course Chip's.
> > >
> > > Could I suggest as others have that you carefully read Chip's page in
> full,
> > > together with previous advice given in this thread. Re-read repeatedly
> until
> > > the light dawns. It will :-)
> > >
> > > Regards,
> > > Peter T
> > >
> > > "MarkTheNuke" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Yes, I did look at the Chip Pearson site. I have finished my VBA
code
> for
> > > > determining if ConditionalFormatting is applied. You can view the
> results
> > > at:
> > > > http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you
> have
> > > any
> > > > questions or comments you can post them to this message.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Did you look at Chip Pearson's site (previously mentioned in this
> > > thread)?
> > > > >
> > > > > Chip Pearson shows a way:
> > > > > http://cpearson.com/excel/CFColors.htm
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > MarkTheNuke wrote:
> > > > > >
> > > > > > Well it looks like there is no way of programatically
determining
> if
> > > > > > conditional formatting is applied, the best to be hoped for is
> > > determining if
> > > > > > conditional formatting should be applied based on the contents
of
> the
> > > cell
> > > > > > and the conditional formatting conditions. I have it all in a
> > > spreadsheet, I
> > > > > > will provide a link once I get it onto my web pages. (In about
2
> > > years)
> > > > > >
> > > > > > "Ken Wright" wrote:
> > > > > >
> > > > > > > LOL :-)
> > > > > > >
> > > > > > > --
> > > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > news:[email protected]...
> > > > > > > > Ok, you caught me there :@ I was looking around and I think
I
> > > might have
> > > > > > > > found a cleaner solution using the Evaluate Method.
However,
> it
> > > still
> > > > > > > does
> > > > > > > > not answer my question about determined if Conditional
> Formatting
> > > is
> > > > > > > applied,
> > > > > > > > I guess the answer is you can't, except by visual
examination.
> > > > > > > > Mark
> > > > > > > <snip>
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > >
> > >
>
>

OK I see your point. In haste to concede "I misread" and beat you to it, I
didn't take a close look at all the implications. I have since though looked
at my own function (I mentioned earlier) that does cater for the scenario
you refer to. FWIW, a tiny snippet:

For each cell in rng
For each fc in cell.formatconditions
' some other stuff
vF1 = Evaluate(fc.Formula1)
If fc.Type = xlExpression Then
bln = vF1
Else
If fc.Operator < 3 Then vF2 = Evaluate(fc.Formula2)
Select Case fc.Operator
' etc
bln = true if relevant condition met in case's

why < 3 (xlEqual), cos only then have Formula2

It's well over a year since I wrote this and forgot all the intricacies.
Apart from the obvious that's in common to all similar functions, mine is
very different from Chip's or any other I've seen. It's not though a UDF and
as such has the luxury to cater for other problems, such as relative vs
absolute addressing in multicell ranges.

In your post that I first replied to, you did clearly stated the problem. As
I've already said I misread, or rather didn't twig. That's despite having
spent some considerable time on a similar function. In mitigation, I
recalled reading your earlier posts in which you had not described the
problem. Namely, in particular respect to the formula in comparison vs
expression. Very difficult for most readers to have picked up on, even those
who have worked with format conditions. You very much gave the impression of
not having read Chip's instructions, eg
"
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.
"
Hence the frustrating dialog that ensued!

Anyway, got there in the end. And wouldn't take much to have adapted Chip's
for your requirements.

Regards,
Peter T

"MarkTheNuke" <[email protected]> wrote in message
news:[email protected]...
> You don't even have to enter a formula, if you enter a cell reference it
will
> fail because it performs a string comparision on the cell address instead
of
> getting the value of the referenced cell. If you use the Evaluate Method,
> you have to use the worksheet object as the calling reference, since if
you
> do use cell addresses they are not resolved to their correct worksheet.
You
> can't enter Sheet2!$A$1 as a conditional value, but you can enter $A$1.
> Like I said, a lot of what I did was refactor the code that Chip provided.
> Also I did paste Chip's code into an excel spreadsheet, unfortunately it
did
> fail when I used my first address.
>
> "Peter T" wrote:
>
> > Perhaps I did misread, quoted from from your previous post:
> >
> > >you can enter a formula into the value comparison routines, which means
you
> > >would have to use the Evaluate Method for the value comparison routine
> >
> > I've never thought to do that - enter a formula to be compared. Quite
> > unusual. In which case simple enough to amend Chip's functions. Along
the
> > lines that if the first character in the string is an "=" it's probably
a
> > formula that's best evaluated.
> >
> > In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's
an
> > if/else. Add an extra Elseif to check that "=" and Evaluate. Would also
need
> > a bit more checking.
> >
> > Regards,
> > Peter T.
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > Comments in line:
> > >
> > > > maybe you misread my original question, or I was not clear
> > >
> > > Could be either / or. Let's find out -
> > >
> > > > and like I said the big thing I was looking for was whether
> > > > ConditionalFormatting should have been applied
> > >
> > > Assuming you are looking at single cell A1 you would enter
> > > =ActiveCondition(A1)
> > >
> > > This will return 0 (no conditions are true) or 1 to 3 to indicate
which of
> > > the three conditions are true. Doesn't that fullfill the requirement?
> > >
> > > > actually I was looking if it was applied,
> > >
> > > Not quite sure what you mean here unless you want to return the
ColorIndex
> > > of Fill or Font that been applied relates to a true condition, or the
> > > underflying index if no conditions are true (btw - a function very
> > > adaptable to own needs)
> > > =ColorIndexOfCF(A1,false)
> > >
> > > Have you put all Chip's functions, including the required helper
> > > GetStrippedValue, into a module and tried each.
> > >
> > > What have I missed?
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > >
> > > "MarkTheNuke" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Yes, Chip does use Evaluate for the expression type of format
> > conditions,
> > > > however, if you play around with conditional formatting, you can
enter a
> > > > formula into the value comparison routines, which means you would
have
> > to
> > > use
> > > > the Evaluate Method for the value comparison routine. Like I said,
I
> > was
> > > > looking for a specific result, which is why I wrote my own
spreadsheet.
> > I
> > > > did not mean to disparage Chips effort, if I did then I apologize.
I
> > just
> > > > made a comment about something I thought was missing.
> > > > I did read Chips routines, more than once, and like I said the big
thing
> > I
> > > > was looking for was whether ConditionalFormatting should have been
> > > applied,
> > > > actually I was looking if it was applied, but there is no function
that
> > > > determines that, maybe you misread my original question, or I was
not
> > > clear
> > > > enough.
> > > >
> > > > "Peter T" wrote:
> > > >
> > > > > A few people have tried to point out to you why you were not
> > succeeding
> > > with
> > > > > Chip's function. And that despite the clear instructions given by
> > Chip.
> > > I'll
> > > > > have a go as well because, having just read your link to your
page, I
> > > was
> > > > > amazed to see your comment:
> > > > >
> > > > > "I was suprised that the author [Chip] did not use the Evaluate
> > Method"
> > > > >
> > > > > I've seen various functions relating to Format Conditions, and
written
> > > one
> > > > > of my own (some different features to anything I've seen). They
all
> > use
> > > > > Evaluate, including of course Chip's.
> > > > >
> > > > > Could I suggest as others have that you carefully read Chip's page
in
> > > full,
> > > > > together with previous advice given in this thread. Re-read
repeatedly
> > > until
> > > > > the light dawns. It will :-)
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > > "MarkTheNuke" <[email protected]> wrote in
message
> > > > > news:[email protected]...
> > > > > > Yes, I did look at the Chip Pearson site. I have finished my
VBA
> > code
> > > for
> > > > > > determining if ConditionalFormatting is applied. You can view
the
> > > results
> > > > > at:
> > > > > > http://home.comcast.net/~mgillesp/Co...Formatting.htm, if
you
> > > have
> > > > > any
> > > > > > questions or comments you can post them to this message.
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Did you look at Chip Pearson's site (previously mentioned in
this
> > > > > thread)?
> > > > > > >
> > > > > > > Chip Pearson shows a way:
> > > > > > > http://cpearson.com/excel/CFColors.htm
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > MarkTheNuke wrote:
> > > > > > > >
> > > > > > > > Well it looks like there is no way of programatically
> > determining
> > > if
> > > > > > > > conditional formatting is applied, the best to be hoped for
is
> > > > > determining if
> > > > > > > > conditional formatting should be applied based on the
contents
> > of
> > > the
> > > > > cell
> > > > > > > > and the conditional formatting conditions. I have it all in
a
> > > > > spreadsheet, I
> > > > > > > > will provide a link once I get it onto my web pages. (In
about
> > 2
> > > > > years)
> > > > > > > >
> > > > > > > > "Ken Wright" wrote:
> > > > > > > >
> > > > > > > > > LOL :-)
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > 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" <[email protected]>
wrote in
> > > > > message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > Ok, you caught me there :@ I was looking around and I
think
> > I
> > > > > might have
> > > > > > > > > > found a cleaner solution using the Evaluate Method.
> > However,
> > > it
> > > > > still
> > > > > > > > > does
> > > > > > > > > > not answer my question about determined if Conditional
> > > Formatting
> > > > > is
> > > > > > > > > applied,
> > > > > > > > > > I guess the answer is you can't, except by visual
> > examination.
> > > > > > > > > > Mark
> > > > > > > > > <snip>
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >