# Conditional Formatting formula problem

1. ## Conditional Formatting formula problem

IF A1 date less than today and B1 = Working
Than A1 date colour RED

IF A1 date from today to next 9 days and B1 = Working
Than A1 date colour ORANGE

I apply below formulas in conditional formula, but not working properly.

=IF(AND(\$A1 < TODAY()),\$D1="WORKING")
RED

=IF(AND(TODAY()+9 > =\$A1),\$B1="WORKING")
ORANGE  Register To Reply

2. ## Re: Conditional Formatting formula problem

In your first formula you have

D1="WORKING"

Your formula should also be this

=(AND(\$A1 < TODAY(),\$B1="WORKING"))

=(AND(TODAY()+9 > =\$A1,\$B1="WORKING"))  Register To Reply

3. ## Re: Conditional Formatting formula problem

Mistakenly but D1, but actually its B1,

RED one working,

but when I apply ORANGE one, then RED one not working  Register To Reply

4. ## Re: Conditional Formatting formula problem  Register To Reply

5. ## Re: Conditional Formatting formula problem

Change the order of the formulas, use the up arrow to make the red one the first formula, the orange one is taking precedent.  Register To Reply

6. ## Re: Conditional Formatting formula problem

Hi

I have used this to create a traffic light system on a spread sheet. I have cheated a little as I fill the cell with green and put two formulas in to change to amber and red

=AND(D4>=TODAY()-89,D4<=TODAY()-75)

=D4<=TODAY()-90

the second one uses todays date as a reference and changes the cell red after 90 days

the first one changes the colour of the cell amber if its between 75-89 days after the avent  Register To Reply

7. ## Re: Conditional Formatting formula problem

Thank you very much  Register To Reply