How do you change cell color automatically based on specific date and date range

Question

I would like to format my spreadsheet so that the cell colors update automatically based on approaching deadlines. I have 6 different scenarios:

blue= 2 weeks before 30 day notice

purple= 1 week before 30 day notice

yellow= 30 day notice

orange= 10 days before IEP date

red= 5 days before IEP date

green= IEP date

All of these rules will be based on the current date, and the dates in the spreadsheet will not change.

I want the entire row to change color together, and I think this is where I am getting stuck, because I can get the one cell to change but not the whole row.

The dates are input manually based deadlines and meetings, but I want the colors to change as the deadlines approach so I have a visual reminder of where I need to be regarding working on that specific student. I am not sure if it makes a difference, but I already have a formula for auto-calculating a students age in age column (F1).

Any help is appreciated!

This is what my spreadsheet looks like currently:

Last MET	Student Name	Birthdate	School Name	Grade	Age	Disability	last IEP	Provider	Notes	30 Day Notice	IEP Date
12/2/2020	student 1	7/12/2012	school	5	11.7	13-Specific Learning Disability	10/6/2022	teacher	cognitive	8/28/2023	10/5/2023
10/20/2020	student 2	9/15/2013	school	4	10.5	15-Autism Spectrum Disorder	12/15/2022	teacher	cognitive	9/7/2023	10/19/2023
3/23/2021	student 3	12/19/2012	school	5	11.2	15-Autism Spectrum Disorder	1/12/2023	teacher	cognitive & adaptive scales	11/14/2023	1/11/2024
3/12/2021	student 4	11/27/2012	school	5	11.3	13-Specific Learning Disability	1/24/2023	teacher	cognitive	11/28/2023	1/23/2024
4/12/2021	student 5	4/8/2015	school	3	8.1	06-Emotional Impairment	3/15/2023	teacher	no testing, use outside evaluation	1/29/2024	3/14/2024

Answer 1

Select the rows that you want to format, from row 2 down.

The active cell in the selection should be in row 2.

I'll assume that the 30 Day Notice is in column J and the IEP Date in column K

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

=$J2<=TODAY()+14

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

Repeat, but with formula

=$J2<=TODAY()+7

and purple as fill color.

Repeat again, with

=$J2<=TODAY()

and yellow. Then with

=$K2<=TODAY()+10

and orange. Next with

=$K2<=TODAY()+5

and red. Finally with

=$K2<=TODAY()

and green.

Answer 2

HI

I followed this precisley but i just want my cells in row 2 to change color when i enter todays date my date will go in k2 and i want a2 b2 c2 and d2 to change color but once i do the formula and put the fill in and click okay my cells change color before i have put any date in