Conditional Formatting - Help Required

[Hai]

Hi

I'm currently having issues with Conditional Formatting. For reference, please refer to uploaded Excel Sheet.

The excel spread sheet is due dates of boiler contractors which are going to end with a particular contractor. I am attempting to colour the dates so I know which are ending soonest and when I need to send out new contracts.

I was attempting to colour the dates;

1)Red if the due date has passed I.e. =$E4<Today()
2)Red if the due date is Today I.e. =$E4=Today()
3)Red if the due date is between Today and 14 days =$E>Today()-14
4)Orange if the due date is between 14 and 28 days (No idea where to go from here)
Red if the due date is over 28 days, but appear green if you type in "Yes" into the completed column (no idea how to do this)

Any help on the matter would be greatly appreciated, if you require further information please let me know.


Kind Regards,

Hai

 

[Asheesh]

Hi Hai...

Were you looking for this...

 

[Hai]

Hi Asheesh,

This was almost what I was looking for.

How would you make the cell red between Todays date + 14 days.I.e. If we were to take todays date of 29/10/14. Then all cells from the 29/10/14 to 12/11/14 would be highlighted as red.

Is it possible to highlight the entire row instead of just the date cell for all colour ranges?

It should go:

Any date before the due date should be red
The date of today should be red
The date from Today + 14 days should be red
The date from +14 days - 28 days should be orange
The date from +28 days should be blank
Entering "Yes" in the field should make the cell/row green.

You have the orange field the wrong way around.

Many thanks for your help so far.

Regards,

Hai

 

[Hui]

To clarify the CF Rules

If you want
1. Red if the Due Date < Today + 2 weeks
2. Orange if the Due Date < Today + 4 weeks
3. Clear if the Due Date > Today + 4 weeks
4. Green if the Column F = Yes

Then add the following CF Rules in order

1.
Use a Formula
=$E4<(TODAY()+28)
Set Format color to Orange

2.
Use a Formula
=$E4<(TODAY()+14)
Set Format color to Red

3.
Use a Formula
=$E4>=TODAY()+28
Set Format color to No Color

4.
Use a Formula
=$F4="Yes"
Set Format color to Green

Now goto CF, Manage Rules
You should see


Click the Stop If True to match the above

Apply

See attached file:

 

[Hai]

Hi Hui,

That's exactly what I was looking for, I appreciate your help.

Is it possible to highlight the entire row, rather than the cell only?

Kind Regards,

Hai

 

[Asheesh]

Check this one...made the changes

 

[Hai]

Hi Asheesh,

Thank you very much for sending that through, VERY HELPFUL!

Thanks for all of your help. Hopefully this will be beneficial for other users!

Regards,

Hai