Colour rows in different shades drawing data from two different columns (tough one)

Hi everyone,

I have been struggling for a while with a pretty complicated task at hand.

Any help with it would be a blessing for me .


REAL LIFE ISSUE:
I have noticed that the mindset in my company toward customer relation is a passive one: “the customer is not chasing” is a common sentence to underline something is not urgent.

I would like to instill in my team a more proactive way of working, “push” rather than “pull”.


WHAT I NEED MY EXCEL SHOULD DO:
We have an internal excel file were where we record all the orders (attached a shortened version).
Each row represents a quotation that needs to be sent out to the customer.
I would like this file to “send signals” when we need to spring to action by giving different shades of colour to the rows (no colour is fine, orange attention, red action needed).


WHAT THE FORMULA SHOULD DO:
Column H, “status”, column F, “Customer expected date” and column AK, “last action date” should be the 3 columns part of the formula.

1.
The formula should filter column H “status” to only include “in progress”, “approved”, “ordered”.
“quotation review pending” should always be in red as it means the final stage is pending and we should rush it out.

2.
The formula should calculate the column F “Customer expected date” ONLY for the statuses in point 1 and: no colour if the customer expected date is 10 days or more away. Yellow colour along the whole row if the date is from 8 to 5 working days away; orange colour if the date is from 5 to 3 working days away; red colour if the date is 3 days away.



3.
The formula should move the colour from red back to orange when the current day’s date is inserted in the column AJ “last action date”.

4.
The formula should re-colour the orange row in red once two days have gone by since the last day’s date.




I know, it's a tall order .

I don't think this is something you can accomplish with one formula. But, I think there are ways to do everything you want to do.

(1) Can be accomplished simply by filtering the columns based on H.

(2) through (4) can be accomplished with conditional formatting rules.

I would recommend applying them to cells, instead of whole rows, for two reasons: (A) to pull the eye towards more specific targets, and (B) to prevent spreadsheet bloat.

As a point, I notice that row F is mostly empty, and they contain "text strings", not "serial numbers representing dates", which will require an added layer of calculation before you can use any of the built-in date functions.

Also, as it stands, rules (2) can contradict or disagree with (3) and (4). If you point at individual cells, it's not a problem, but if you really want it row-wise....

EDIT:
I played around a little; try the attached on for size. It does (1) and (2), but I don't really get (3) and (4), and the data sample for that row was blank anyway.

Hmm anybody willing to take on this challenged if I'd put up a symbolic prize of 10$ (via Paypal) to the person who could manage to implement such a system?