Help with Conditional Formatting

[CS Ireland]

Hi Folks,

Im new to the site, and will appreciate any help that you can give! Thanks...

I work for a Construction company that recieves daily maintenance jobs from our clients that have target dates. I am looking for a tailored solution to my problem please:

I basically what to open my spreadsheet every day and see the status of each job by colour (traffic light system), even if it is the word 'STATUS' is beside each date - green (current date is more than 3 days before the target date), amber (current date within 3 days of target) and red (current date past target date).

I have tried inputting formulas through Conditional Formatting ie Rule 1 =$C3="STATUS", Rule 2 =AND(MEDIAN(TODAY()+1,$B3,TODAY()+3)=$B3,$C3<>"STATUS) etc etc as read on post from another website.

This isnt working!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TODAYS DATE
[/TD]
[TD]3/9/2013
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Address
[/TD]
[TD]Target Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1, Main Street
[/TD]
[TD]7/9/13
[/TD]
[TD]STATUS
[/TD]
[/TR]
</tbody>[/TABLE]

So basically my table is as above. I would like 'STATUS' to be green, because it is within todays date. When i open my spreadsheet tomorrow the date will be 4/9/13 so i want 'STATUS' to change to Orange, if possible!

I will settle for any alternatives! pretty much to make my life easier with the 'traffic light' system.

Again, any help would be much appreciated.

Thank-you

Steve

 

[Jeffrey Smith]

Maybe it would be easier to create another column that does the math and use that column for the conditional formatting. You could call it Days Due/Past

=INT(B3-Now())

Where B3 is the target date. The conditional formatting would easier. The only thing I can think of otherwise is to create a macro.

Jeff