Using conditional formatting to auto colour cells

[Goddess]

Hi all!,

I have a calendar which I need to keep track of project timeline, which changes every month or so. Is there a way to auto colour the corresponding cells based on the date given?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Fruits	Date	Jan-22					Feb-22				Mar-22
3				3	10	17	24	31	7	14	21	28	7	14	21	28
4		Apple	5/1/2022
5		Grape	6/1/2022
6		Apple/Grape	8/2/2022
7		Apple/Grape/Orange	18/3/2022
8		Apple	20/3/2022
9		Apple	28/3/2022
10		Grape	29/3/2022
11
Sheet1

For example, when I enter the date in column C, cells in column D till P will change colour based on the date range (the date on each month begins on a Monday), and the colour will also depends on what is written in column B (anything with Apple is represented by yellow colour, whereas other text will be represented by grey colour).

So for cell C4, the correct cell to highlight is D4, since 3 is the range for 3rd - 9th of January 2022.

Thanks!

 

[jtakw]

Hi,

This should work, but you need to populate D2:P2 with Date in every cell (e.g. 1/1/2022 for D2:H2, 2/1/22 for I2:L2, etc.)

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Fruits	Date	Jan-22	Jan-22	Jan-22	Jan-22	Jan-22	Feb-22	Feb-22	Feb-22	Feb-22	Mar-22	Mar-22	Mar-22	Mar-22
3				3	10	17	24	31	7	14	21	28	7	14	21	28
4		Apple	1/5/2022
5		Grape	1/6/2022
6		Apple/Grape	2/8/2022
7		Apple/Grape/Orange	3/18/2022
8		Apple	3/20/2022
9		Apple	3/28/2022
10		Grape	3/29/2022
Sheet949

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:P10	Expression	=AND(WEEKNUM($C4,2)=WEEKNUM(DATE(YEAR(D$2),MONTH(D$2),D$3),2),COUNTIF($B4,"*Apple*")=0)	text	NO
D4:P10	Expression	=AND(WEEKNUM($C4,2)=WEEKNUM(DATE(YEAR(D$2),MONTH(D$2),D$3),2),COUNTIF($B4,"*Apple*"))	text	NO

 

[jtakw]

If you want to Keep your current format, an alternative to my suggestion above, would be to put Actual Dates in D3:P3, format D3:P3 custom "d" (without quotes):

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Fruits	Date	Jan-22					Feb-22				Mar-22
3				3	10	17	24	31	7	14	21	28	7	14	21	28
4		Apple	1/5/2022
5		Grape	1/6/2022
6		Apple/Grape	2/8/2022
7		Apple/Grape/Orange	3/18/2022
8		Apple	3/20/2022
9		Apple	3/28/2022
10		Grape	3/29/2022
Sheet949 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:P10	Expression	=AND(WEEKNUM($C4,2)=WEEKNUM(D$3,2),COUNTIF($B4,"*Apple*")=0)	text	NO
D4:P10	Expression	=AND(WEEKNUM($C4,2)=WEEKNUM(D$3,2),COUNTIF($B4,"*Apple*"))	text	NO

 

[Peter_SSs]

Sticking with you original layout, I think this adaptation of @jtakw's suggestion should also work.

22 01 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Fruits	Date	Jan-22					Feb-22				Mar-22
3				3	10	17	24	31	7	14	21	28	7	14	21	28
4		Apple	5/01/2022
5		Grape	6/01/2022
6		Apple/Grape	8/02/2022
7		Apple/Grape/Orange	18/03/2022
8		Apple	20/03/2022
9		Apple	28/03/2022
10		Grape	29/03/2022
Timeline

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:P10	Expression	=AND($C4-(LOOKUP(99999,$D$2:D$2)+D$3-1)>=0,$C4-(LOOKUP(99999,$D$2:D$2)+D$3-1)<7,COUNTIF($B4,"*apple*")=0)	text	NO
D4:P10	Expression	=AND($C4-(LOOKUP(99999,$D$2:D$2)+D$3-1)>=0,$C4-(LOOKUP(99999,$D$2:D$2)+D$3-1)<7,COUNTIF($B4,"*apple*"))	text	NO

 

[Goddess]

Thank you both so much! Made my life so much easier without having to manually change the colours every time the date changes!! :D :D :D

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[jtakw]

You're welcome.