Conditional formatting to fill between dates

[gogi66]

Hi team,

I would have a question regarding conditional formating.

I am trying to prepare gantt chart and based on the picture below.
Currently I am struggeling to fill cells from R to X based on the start and finish date.

Any ideas would be helpful

Thank you in advance.

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
11						52	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
12	1/1/2022	1	2/7/2022	7
13	1/3/2022	2	2/14/2022	8
14	1/10/2022	3	2/21/2022	9
15	1/23/2022	5	2/28/2022	10
Sheet24

Cell Formulas
Range	Formula
B12:B15,D12:D15	B12	=WEEKNUM(A12)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F12:X15	Expression	=AND($B12<=F$11,$D12>=F$11)	text	NO

 

[kvsrinivasamurthy]

Formula for CF in F2:

=F$1=Median($B2,$D2,F$1)

copied format to all cells.

 

[gogi66]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
11						52	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
12	1/1/2022	1	2/7/2022	7
13	1/3/2022	2	2/14/2022	8
14	1/10/2022	3	2/21/2022	9
15	1/23/2022	5	2/28/2022	10
Sheet24

Cell Formulas
Range	Formula
B12:B15,D12:D15	B12	=WEEKNUM(A12)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F12:X15	Expression	=AND($B12<=F$11,$D12>=F$11)	text	NO

Works perfectly! thank you

 

[gogi66]

Formula for CF in F2:

=F$1=Median($B2,$D2,F$1)

copied format to all cells.

Elegant approach, but if i have emtpy cells in B or D columns then it automatically fills

 

[kvsrinivasamurthy]

Elegant approach, but if i have emtpy cells in B or D columns then it automatically fills

Try this
=AND($B2<>"",$D2<>"",F$1=Median($B2,$D2,F$1))