Conditional Formatting Cells based on dates with colours

[JoelBrown1990]

Hi there,



I am looking for some help with conditional formatting.



I need to highlight a row of cells based on dates within certain columns.



There are five rules I require:

1. The row of cells to highlight yellow if the date in cell “F” is between one week and two weeks after today’s date.
2. The row of cells to highlight orange if the date in cell “F” is less than a week after today’s date.
3. The row of cells to highlight red if the date in cell “F” is before today’s date.
4. The row of cells to highlight green once a date has been entered into cell “H”.
5. The row of cells to remain blank until one of the above comes into action.

This is for a Request for Information schedule. I am happy to send a copy of the schedule if this will make it easier.



Many thanks in advance for your help.



Kind regards,
Joel

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County
2	ST10 3DT	Yes	53.02851	-1.91721	405650	01/11/2021	SK056479	A
3	BD6 1EW	Yes	53.75919	-1.76996	415264	28/11/2021	SE152292
4	DY3 1RG	Yes	52.54611	-2.12357	391717	29/11/2021	SO917942	A
5	S18 8UA	Yes	53.30377	-1.50596	433020	30/11/2021	SK330786
6	DL11 6JD	Yes	54.38196	-1.96708	402236	06/12/2021	SE022984
7	HP23 6DE	No	51.77818	-0.65183	493107	07/12/2021	SP931096
8	EX17 5AG	Yes	50.75695	-3.84388	270038	14/12/2021	SX700968
Lists

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:H43	Expression	=$H2<>""	text	NO
A2:H43	Expression	=AND($F2<>"",$F2<TODAY())	text	NO
A2:H43	Expression	=AND($F2<>"",$F2<TODAY()+7)	text	NO
A2:H43	Expression	=AND($F2<>"",$F2<TODAY()+14)	text	NO

 

[JoelBrown1990]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County
2	ST10 3DT	Yes	53.02851	-1.91721	405650	01/11/2021	SK056479	A
3	BD6 1EW	Yes	53.75919	-1.76996	415264	28/11/2021	SE152292
4	DY3 1RG	Yes	52.54611	-2.12357	391717	29/11/2021	SO917942	A
5	S18 8UA	Yes	53.30377	-1.50596	433020	30/11/2021	SK330786
6	DL11 6JD	Yes	54.38196	-1.96708	402236	06/12/2021	SE022984
7	HP23 6DE	No	51.77818	-0.65183	493107	07/12/2021	SP931096
8	EX17 5AG	Yes	50.75695	-3.84388	270038	14/12/2021	SX700968
Lists

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:H43	Expression	=$H2<>""	text	NO
A2:H43	Expression	=AND($F2<>"",$F2<TODAY())	text	NO
A2:H43	Expression	=AND($F2<>"",$F2<TODAY()+7)	text	NO
A2:H43	Expression	=AND($F2<>"",$F2<TODAY()+14)	text	NO

Hi Fluff,

Thank you for your response. I will try this and let you know if it works ok.

Can I copy the formula for multiple rows? So for F2 down to F1000 for example?

Many thanks,
Joel

 

[Fluff]

Just select the range you want it to work on & then add the rules.
You need to ensure that the rules are in the same order as shown in post#2