Conditional formation in planing calendar

[phdbergman]

Hi Forum,

I'm reach out to you to ask if someone can help suggest improvement to a function for conditional formatting.
I created a calendar where I can plan resources by entering the number of days and the following cells are colored by using conditional formatting.

This far I have only been able to solve the problem by IFS-function and have a ugly solution for up to 21 days. I would like to expend the range to whatever really.
The cells are only to be colored for integer values.

Present formula:
=IFS( AND(ISNUMBER(OFFSET(E13;0;-20));OFFSET(E13;0;-20)>20);1; AND(ISNUMBER(OFFSET(E13;0;-19));OFFSET(E13;0;-19)>19);1; AND(ISNUMBER(OFFSET(E13;0;-18));OFFSET(E13;0;-18)>18);1; AND(ISNUMBER(OFFSET(E13;0;-17));OFFSET(E13;0;-17)>17);1; AND(ISNUMBER(OFFSET(E13;0;-16));OFFSET(E13;0;-16)>16);1; AND(ISNUMBER(OFFSET(E13;0;-15));OFFSET(E13;0;-15)>15);1; AND(ISNUMBER(OFFSET(E13;0;-14));OFFSET(E13;0;-14)>14);1; AND(ISNUMBER(OFFSET(E13;0;-13));OFFSET(E13;0;-13)>13);1; AND(ISNUMBER(OFFSET(E13;0;-12));OFFSET(E13;0;-12)>12);1; AND(ISNUMBER(OFFSET(E13;0;-11));OFFSET(E13;0;-11)>11);1; AND(ISNUMBER(OFFSET(E13;0;-10));OFFSET(E13;0;-10)>10);1; AND(ISNUMBER(OFFSET(E13;0;-9));OFFSET(E13;0;-9)>9);1; AND(ISNUMBER(OFFSET(E13;0;-8));OFFSET(E13;0;-8)>8);1; AND(ISNUMBER(OFFSET(E13;0;-7));OFFSET(E13;0;-7)>7);1; AND(ISNUMBER(OFFSET(E13;0;-6));OFFSET(E13;0;-6)>6);1; AND(ISNUMBER(OFFSET(E13;0;-5));OFFSET(E13;0;-5)>5);1; AND(ISNUMBER(OFFSET(E13;0;-4));OFFSET(E13;0;-4)>4);1; AND(ISNUMBER(OFFSET(E13;0;-3));OFFSET(E13;0;-3)>3);1; AND(ISNUMBER(OFFSET(E13;0;-2));OFFSET(E13;0;-2)>2);1; AND(ISNUMBER(OFFSET(E13;0;-1));OFFSET(E13;0;-1)>1);1; AND(ISNUMBER(E13);E13>0);1)

Perhaps a recursive formula, array-formula, completely different solution?
VBA is unfortunacty a no go since it is blocked from execution.

Best regards
Björn

 

[mart37]

Can you use an extra sheet as a kind off template for your CF-conditions?

 

[mart37]

With an extra sheet (and an empty column on the left on sheet CF DATA):

Map4
	A	B	C	D	E	F	G	H	I	J	K	L
1		1-jan	2-jan	3-jan	4-jan	5-jan	6-jan	7-jan	8-jan	9-jan	10-jan	11-jan
2		vr	za	zo	ma	di	wo	do	vr	za	zo	ma
3	1			5
4	2		3
5	3	2
6	4			2				4
7	5
8	6								2
9	7				2
CALENDAR

Cell Formulas
Range	Formula
B2:L2	B2	=B1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:Z10	Expression	='CF DATA'!B3>0	text	NO

Map4
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		-1	-2	5	4	3	2	1	0	-1	-2	-3
4		-1	3	2	1	0	-1	-2	-3	-4	-5	-6
5		2	1	0	-1	-2	-3	-4	-5	-6	-7	-8
6		-1	-2	2	1	0	-1	4	3	2	1	0
7		-1	-2	-3	-4	-5	-6	-7	-8	-9	-10	-11
8		-1	-2	-3	-4	-5	-6	-7	2	1	0	-1
9		-1	-2	-3	2	1	0	-1	-2	-3	-4	-5
10		-1	-2	-3	-4	-5	-6	-7	-8	-9	-10	-11
CF DATA

Cell Formulas
Range	Formula
B3:L10	B3	=IF(CALENDAR!B3<>"",CALENDAR!B3,A3-1)

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2			5
3						4						4
4
5										8
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:T5	Expression	=LOOKUP(2,1/($C2:C2<>""),$C2:C2)+LOOKUP(2,1/($C2:C2<>""),COLUMN($C2:C2)-COLUMN($C2))>=COLUMNS($C2:C2)	text	NO

 

[phdbergman]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2			5
3						4						4
4
5										8
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:T5	Expression	=LOOKUP(2,1/($C2:C2<>""),$C2:C2)+LOOKUP(2,1/($C2:C2<>""),COLUMN($C2:C2)-COLUMN($C2))>=COLUMNS($C2:C2)	text	NO

Yes, this is the kind of solution I'm looking for. Now only to adopt only to react to numbers, preferably integers, and leave with text unnoticed?

 

[Fluff]

If you want to ignore text use

=LOOKUP(2,1/($C2:C2<>"")/(ISNUMBER($C2:C2)),$C2:C2)+LOOKUP(2,1/($C2:C2<>"")/(ISNUMBER($C2:C2)),COLUMN($C2:C2)-COLUMN($C2))>=COLUMNS($C2:C2)