Conditional Formatting

[Shawn]

I have two sheets (Sheet1 and Sheet2). In each sheet I
have a range with data (A1:Z23). I have several cells on
each page conditionally formatted to change to red if
certain things happen. When the person using the file is
done they hit a "complete button" embedded on the page.

I would like VBA code that looks at Sheet1 Range A1:Z23
and Sheet2 Range A1:Z23 and will find any cells of which
have been changed to red due to the conditional formatting
being triggered. If no cells are red, then nothing. If
any cell is red then there would be a VBA OKOnly message
box saying, "Please look for the error".

 

[Bernie Deitrick]

Shawn,

AFAIK, there is no programatic way to determine when the conditional
formatting has been applied.

The easiest way for you would be to link the conditional formatting to a
formula referencing a cell rather than using the "Cell Value is" option.

For example, if you wanted to color cell A1 red when it's value was greater
than 10, say, you could use this formula in cell B1

=A1>10

and in the conditional formatting for cell A1, use the "Formula is" option,
with the formula:

=B1

Set your formatting as normally, then in your code, you could check all of
the other cells (B1 in this example) for TRUE and use that as the trigger of
your message box.

HTH,
Bernie
MS Excel MVP