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

 

[Norie]

I think you would need to add conditional formatting for all the cells that don't currently have it and alter the formulas of the ones that do.