I have some code that checks the Interior.ColorIndex of a cell and
acts upon the result. No problem.

I thought that I could alter the Pattern colour (i.e.
Interior.ColorIndex) with a conditional format. I have. No problem.

However the code that acts upon the Interior.ColorIndex does not
detect the changed ColorIndex when it is altered due to the
Conditional Formatting. It reads the value that is set when no
conditional formatting is active (even if it is).

So I need to detect for a conditional format as well. However
..FormatConditions method does not provide any way to read which if any
of the FormatConditions is currently active.

[Aside] Ideally .Interior would return what is currently the case due
to conditional formatting or not. Another method say .InteriorDefault
would yield what we are currently getting. Anyway this is not the case
so I'm fishing for a work around.

Any suggestions

Hi Chris,

Check instead for the conditional format condition(s).


---
Regards,
Norman



"Chris" wrote in message
m...
I have some code that checks the Interior.ColorIndex of a cell and
acts upon the result. No problem.

I thought that I could alter the Pattern colour (i.e.
Interior.ColorIndex) with a conditional format. I have. No problem.

However the code that acts upon the Interior.ColorIndex does not
detect the changed ColorIndex when it is altered due to the
Conditional Formatting. It reads the value that is set when no
conditional formatting is active (even if it is).

So I need to detect for a conditional format as well. However
.FormatConditions method does not provide any way to read which if any
of the FormatConditions is currently active.

[Aside] Ideally .Interior would return what is currently the case due
to conditional formatting or not. Another method say .InteriorDefault
would yield what we are currently getting. Anyway this is not the case
so I'm fishing for a work around.

Any suggestions

Checking for the condition(s) in the code is no viable because the
conditions are not of that sell but of other relative cells. Add/Remove
a column or row and the code will be out. There are ways round this with
named ranges but it is getting bigger and uglier to solve what should be
simple.

It is however the only solution so far.

The other way is to read the condition formula and parse it / interprit
it so check for the condition 'again' in code. Any code tips how to
achieve this?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Put on your hardhat and visit Chip Pearson's site:

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


Chris wrote:

I have some code that checks the Interior.ColorIndex of a cell and
acts upon the result. No problem.

I thought that I could alter the Pattern colour (i.e.
Interior.ColorIndex) with a conditional format. I have. No problem.

However the code that acts upon the Interior.ColorIndex does not
detect the changed ColorIndex when it is altered due to the
Conditional Formatting. It reads the value that is set when no
conditional formatting is active (even if it is).

So I need to detect for a conditional format as well. However
.FormatConditions method does not provide any way to read which if any
of the FormatConditions is currently active.

[Aside] Ideally .Interior would return what is currently the case due
to conditional formatting or not. Another method say .InteriorDefault
would yield what we are currently getting. Anyway this is not the case
so I'm fishing for a work around.

Any suggestions

--

Dave Peterson

It makes no difference where the conditions are, you just check anyway.

For instance, if the CF says =E17=12, then you check E12 in the code, not
the cell where the CF is.

Testing for the CF colorindex is remarkably tricky, and not robust. Norman's
suggestion is sounder.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris" wrote in message
...
Checking for the condition(s) in the code is no viable because the
conditions are not of that sell but of other relative cells. Add/Remove
a column or row and the code will be out. There are ways round this with
named ranges but it is getting bigger and uglier to solve what should be
simple.

It is however the only solution so far.

The other way is to read the condition formula and parse it / interprit
it so check for the condition 'again' in code. Any code tips how to
achieve this?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!