Highlight rows based on date

Question

Hello I'm just getting back into excel and my skills are very rusty so I come seeking some advice.

I've got a spreadsheet with columns A through M, and it's 143 rows right now. What I'm trying to do is to set up a formula to turn an entire row red or yellow depending on whether it's within 30 or 60 days of the date in column K

I can turn the cell in column K, red or yellow based on the date by using this formula =TODAY()+30, or +60, but I would like to turn the entire row red or yellow, so those stand out a little more prominent than just a cell

Thanks for any help.

Answer 1

Assuming that row 1 contains headers, select rows 2 to 143 or further down. The active cell in the selection should be in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($K2<>"", $K2<=TODAY()+60)

Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=AND($K2<>"", $K2<=TODAY()+30)

and red as fill color.

Answer 2

Thanks for the quick reply.

I think I'm doing something wrong b/c I can only get B8 to change when I put in the formula.

You were right about there being headers, the first two rows are headers.

Here's what I'm doing. I go to the ribbon and choose conditional formatting, new rule, use formula to determine what cells to format.

I add the following formula

=AND($K3<>"", $K3<=TODAY()+60)

Choose yellow, click okay, click okay again, and cell B8 turns yellow.

I was expecting rows 3 through 17 to turn yellow, b/c they all have dates of 6/4 or 6/24 which would put them within the 60 day window to be yellow.

Answer 3

Delete the current rule(s).

Select rows 3 to 143.

Make sure that the active cell in the selection is in row 3, for example A3 (you can check this in the name/address box on the left hand side of the formula bar).

Then create rules with formula

=AND($K3<>"", $K3<=TODAY()+60)

and

=AND($K3<>"", $K3<=TODAY()+30)