Nested IF statement using conditional formatting

[triplel1977]

I want to create an IF statement (possibly an AND statement) for the following conditions using Conditional Formatting, so I can highlight only the dates that are past due over 45 on a 5000 line spreadsheet.

Column A value equals IN
Column D value equals =TODAY-45

I can create the =TODAY formula in column D and get it to work and it highlights the all dates in the spreadsheet, but when I try to get the result to only be highlighted if the value in column A = "IN" I either get no result or error messages.

Can anybody provide a simple formula that will solve this problem? I use Excel 2007.

Thanks

 

[barry houdini]

AND should work for you, something like

=AND(A1="In",D1< TODAY()-45)

 

[triplel1977]

I should be be able to use =AND($A:$A="In",$D:$D< TODAY()-45), right?
since I am going to create this conditional formatting as part of a macro, so I don't have to apply this formula to this report every day.

I didn't bring this report home since it is proprietory, so I will check it out tomorrow and let you know how it goes.

Thanks you very much.

 

[barry houdini]

Normally with conditional formatting you just apply the formula to the first row and then it will adjust to all the other rows, so the formula I suggested should be sufficient, as long as you specify that the range to which it applies begins on row 1

 

[triplel1977]

I'll give it a shot tomorrow.
Thanks for all your help

 

[triplel1977]

It worked!!!! Happy dance. I've already added to my macro. Thanks again, you made my job more productive.
Lee