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

[Guest]

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.

 

[Ken Wright]

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

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

 

[Guest]

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?

 

[Dave Peterson]

Chip Pearson shows a way:

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

 

[Guest]

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.

 

[Ken Wright]

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.
this page<<<

 

[Guest]

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]

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

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>

 

[Guest]

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)

 

[Dave Peterson]

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

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

 

[Guest]

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/ConditionalFormatting.htm, if you have any
questions or comments you can post them to this message.

 

[Peter T]

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

 

[Guest]

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.

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

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/ConditionalFormatting.htm, if you have any
questions or comments you can post them to this message.

 

[Peter T]

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

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.

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

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/ConditionalFormatting.htm, if you

have
any

questions or comments you can post them to this message.

:

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)

:

LOL

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

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

 

[Peter T]

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.

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

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.

succeeding

with

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

Yes, I did look at the Chip Pearson site. I have finished my VBA

code

for

determining

if

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)

:

LOL

----------------------------------------------------------------------------------------------------------------------------------------------------

--

message
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

 

[Peter T]

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.

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

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.

succeeding

with

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

Yes, I did look at the Chip Pearson site. I have finished my VBA

code

for

determining

if

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)

:

LOL

----------------------------------------------------------------------------------------------------------------------------------------------------

--

message
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

 

[Guest]

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.

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.

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

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.

:

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

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/ConditionalFormatting.htm, if you have
any
questions or comments you can post them to this message.

:

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)

:

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

--------------------------------------------------------------------------
--

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

 

[Peter T]

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

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.

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.

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



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.

:

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

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/ConditionalFormatting.htm, if you
have
any
questions or comments you can post them to this message.

:

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)

:

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

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