Conitional Formating of entire row based on expiration dates

[theemann77479]

I have a spreadsheet that has "authorizations dates" for clients in them. I want it to highlight the entire row of the clients information, in yellow when their auth period is 21 days out from expiring and then red when it is 7 days from expiring. Everything I have tried is failing miserably. I have been able to make the single cell change colors but then when I try and apply it to the entire row it is only reading the cell at the top and formatting the entire column based off that date. To apply it to all I would have to individually input the formula for each and that would extremely time consuming. Any help or advice is appreciated.

*I am no expert on the use of excel but can do simple formulas. Office 2010.

[etaf]

you should use a $ in the formula(s) to fix the cell and then apply to the range

so
if the date was in A2

formula
=AND($a2>=today()-21, $a2<today()-7) for yellow


=AND($a2>=today()-7, $a2<=today()) for red


for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
select the range you want to apply to ( make sure in the formula below - you use the same row number as your selection
so if you chose D4:H18, then the row in the formula should be 4

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=AND($a2>=today()-21, $a2<today()-7)

Format… [Number, Font, Border, Fill] - fill for yellow
choose the format you would like to apply when the condition is true
OK >> OK

[theemann77479]

That was perfect! Thank you so much. I had been trying to figure this out for quite awhile.

Sincerely,
Jon

[etaf]

your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation her

[theemann77479]

Ok, I have no idea what has happened to this worksheet in the past week but I thought it was worked out. I am attaching a file that is stripped down for privacy reasons. You will see that the conditional formatting is entered and applied to the correct fields but it does not appear to be changing the fill colors as the dates change.

Again, my intent is for the entire row to highlight yellow when the "auth end date" (d4) is 21 days out and then to turn red when it is 7 days out or expired. I think this may be some sort of locked cell issue? I can't seem to figure it out. Thoughts?

[etaf]

all those dates are in the future

so do you want future dates if so then we need to change the formula

RED
=AND($D4<>"",$D4<=TODAY()+7) - which will include dates in the past

YELLOW
=AND($D4<=TODAY()+21, $D4>TODAY()+7)

sorry about that , i was testing the past and not the future

hope the attached is what you are after

[theemann77479]

That worked perfectly. Thanks again for your help! Take care.

[sovero]

Hi have a similar problem but think mine is a bit more complex. Seeing that you (etaf) are such a bright mind I would like to ask you to help me as well. The link to my tread is

http://www.excelforum.com/excel-form...ml#post3611949