VBA override of Conditional Format

[Benjaminmin]

Hi all,

I am creating an excel sheet with a workflow chart, as can be seen in this picture:

As you can see the actions (columns E,H,K) are color coded depending on whether or not they have passed their "do-by-date", respectively, green for before, yellow for at and red for after the date.

What I want to obtain is to make the status modifier (drop down list in column N) override the colors, so that if it changed from "pending" to "complete", the whole row goes green (since otherwise older jobs would get flagged all red). And correspondingly if it gets set to "Suspended" the whole row goes grey.

My problem is that I have already used all 3 cond. formats to make the red/yellow/green, so I was wondering if there is some VBA code or something that could override it? Or if there is another alternative (for example tick box to flag complete, which then disables the formatting and colors it green (if complete) or grey (if suspended).

Thank you very very much in advance!

best,
Benjaminmin

 

[Joe4]

You may be able to create Event Procedure VBA code to do this, specifically the Worksheet_Change event which is automatically triggered whenever specified ranges on your sheet are updated. There are lots of links on the internet that have examples. Here is one: http://answers.microsoft.com/en-us/...rmatting/ddb6cd95-2530-46b3-87b0-421e63f43a48

Also, here is a good link on Event Procedures: http://www.cpearson.com/excel/Events.aspx

 

[mathchick]

I don't think there's a restriction on the number of conditional formats. Why not just make another conditional format that if Status = Suspended then whole row is green and click the "stop if true' so the rest don't evaluate and change the color?

 

[Benjaminmin]

I don't think there's a restriction on the number of conditional formats. Why not just make another conditional format that if Status = Suspended then whole row is green and click the "stop if true' so the rest don't evaluate and change the color?

Yes there is a restriction of 3 conditional formats.

@Joe4, thank you for your suggestion, I think youre on to something here. However I have tried to play with it a little and I don't understand how I would set the three cases (i.e. the N column being Pending, Suspended or Completed) and then getting them to color that row.

If anyone can help me further with this it would be immensely appreciated.

Thanks!

 

[mathchick]

Another option, I think, would be to color all of the cells green and then set up conditional formatting only for exceptions. OR might be your best friend in this case.

 

[Joe4]

If anyone can help me further with this it would be immensely appreciated.

I will take a look at this today when I get a little time to recreate your worksheet and set up the conditions.

 

[Jonmo1]

Strictly speaking, Excel 2003 is NOT limited to 3 conditions...
It is limited to 3 conditional FORMATS..

You can use unlimited conditions..but only 3 formats...
it just depends on how you write the formulas..


You already have a conditional format that turns green...All you need to do is add a condition to that format..
From the picture you posted, that is
=AND($E$2 < G9,G9 < > "")

Since your new condtion is if Column N = "Completed", then the whole row goes green...
You could change that formula to

=OR($N6="Completed",AND($E$2 < G9,G9 < > ""))

 

[mathchick]

you could change your green conditional formatting to:

=OR(AND($E$2<G9,G9<>"),N9="Suspended")

 

[Joe4]

Strictly speaking, Excel 2003 is NOT limited to 3 conditions...
It is limited to 3 conditional FORMATS..

You can use unlimited conditions..but only 3 formats...
it just depends on how you write the formulas..

You already have a conditional format that turns green...All you need to do is add a condition to that format..

Good point.

So you might not need VBA code after all. Let us know if you still have issues getting it to work.

 

[Jonmo1]

=OR($N6="Completed",AND($E$2 < G9,G9 < > ""))

That should have been N9..