recognizing conditional formatting with VBA

[c1802362]

Hello group.

Came across a situation today which I couldn't resolve to my
satisfaction. Maybe someone here can help...

A colleague needed me to code a spreadsheet with a fairly simple
requirement. If a certain cell was green, the entire row would be
colored green; if the cell was red, the entire row would be shaded
red. The issue is the cell I'm basing everything off of is
conditionally formatted. Trying to recognize the cell's color via
cell.interior.colorindex does not work.

I was unable to get any code to recognize the conditionally formatted
cell. I finally resolved my problem by eliminating the conditional
formatting and hardcoding the conditionally formatted formulas into
the VBA module.

So, does anyone know of a method to get VBA to recognize a
conditionally formatted cell?

Art

 

[Rick Rothstein]

You cannot "see" the formatting resulting being applied to a cell from
Conditional Formatting directly. The best you can do is have your code
perform the same test that the Conditional Formatting is doing... if your
test is True then so is the one the Conditional Formatting is evaluating, so
then you know it is applying whatever formatting to the cell that was
specified.

Rick Rothstein (MVP - Excel)


"c1802362" wrote in message

Hello group.

Came across a situation today which I couldn't resolve to my
satisfaction. Maybe someone here can help...

A colleague needed me to code a spreadsheet with a fairly simple
requirement. If a certain cell was green, the entire row would be
colored green; if the cell was red, the entire row would be shaded
red. The issue is the cell I'm basing everything off of is
conditionally formatted. Trying to recognize the cell's color via
cell.interior.colorindex does not work.

I was unable to get any code to recognize the conditionally formatted
cell. I finally resolved my problem by eliminating the conditional
formatting and hardcoding the conditionally formatted formulas into
the VBA module.

So, does anyone know of a method to get VBA to recognize a
conditionally formatted cell?

Art

 

[c1802362]

You cannot "see" the formatting resulting being applied to a cell from
Conditional Formatting directly. The best you can do is have your code
perform the same test that the Conditional Formatting is doing... if your
test is True then so is the one the Conditional Formatting is evaluating,so
then you know it is applying whatever formatting to the cell that was
specified.

Rick Rothstein (MVP - Excel)

thanks - I had a suspicion the answer was going to be something along
the lines of your response...

Art

 

[Gord Dibben]

See Chip's site.

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


Gord Dibben MS Excel MVP