Conditional Formate Question

[Joneye]

I have a traffic light system that im struggling with.

Condition 1
Formula is ------ = E6 < now () CELL TURNS RED

Condition 2
Cell Value is, equal to ------ "Completed" CELL TURNS GREEN.

Problem area is that I have some data validation in colcum F (Booked & Completed are the options)

The ideal solution is.

If the date is today and showing as booked then the cell is Orange.
If the date is yesterdays date and showing as booked then the cell is RED
If the date is in and showing as COMPLETED then the cell is Green.
If No date then clear.

Any help would assist?

Note the below data -- sadly no colours have come through using the HTMl thingy

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">09/08/2011</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Booked</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">07/08/2011</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Completed</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">09/08/2011</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Completed</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">07/08/2011</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Booked</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">07/08/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">1. Dashboard</p><br /><br />

 

[moonfish]

Apply these to $E:$F with Use a formula to determine which cells to format:

=$F1="Completed" - Green fill
=AND($E1 < TODAY(),$F1="Booked") - Red fill
=AND($E1 = TODAY(),$F1="Booked") - Orange fill

 

[Joneye]

Ive done it but still it shows as white?

I put the detail in the conditional formatting. Did it work ok your end?

 

[moonfish]

Yea, I tested this before posting, works for me.

Are you applying this to the entire columns E:F, or to a part of that column?
If you apply it to E6:F100, you need to change $E1 and $F1 in the conditions to $E6 and $F6, the top cell in the affected area.

 

[Joneye]

AMAZING,

Got it in one, many many thanks for your help....