VBA override of Conditional Format

Benjaminmin

Board Regular
Joined
Nov 20, 2009
Messages
116
Hi all,

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


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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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 < > ""))
 
Last edited:
Upvote 0
you could change your green conditional formatting to:
Code:
=OR(AND($E$2<G9,G9<>"),N9="Suspended")
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,881
Members
447,883
Latest member
bennazi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top