# Conditional formation in planing calendar

#### phdbergman

##### New Member
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### mart37

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

#### mart37

##### Well-known Member
With an extra sheet (and an empty column on the left on sheet CF DATA):

Map4
ABCDEFGHIJKL
11-jan2-jan3-jan4-jan5-jan6-jan7-jan8-jan9-jan10-jan11-jan
315
423
532
6424
75
862
972
CALENDAR
Cell Formulas
RangeFormula
B2:L2B2=B1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:Z10Expression='CF DATA'!B3>0textNO

Cell Formulas
RangeFormula
B3:L10B3=IF(CALENDAR!B3<>"",CALENDAR!B3,A3-1)

#### Fluff

##### MrExcel MVP, Moderator
Another option
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTU
1
25
344
4
58
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:T5Expression=LOOKUP(2,1/(\$C2:C2<>""),\$C2:C2)+LOOKUP(2,1/(\$C2:C2<>""),COLUMN(\$C2:C2)-COLUMN(\$C2))>=COLUMNS(\$C2:C2)textNO

#### phdbergman

##### New Member
Another option
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTU
1
25
344
4
58
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:T5Expression=LOOKUP(2,1/(\$C2:C2<>""),\$C2:C2)+LOOKUP(2,1/(\$C2:C2<>""),COLUMN(\$C2:C2)-COLUMN(\$C2))>=COLUMNS(\$C2:C2)textNO
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

##### MrExcel MVP, Moderator
If you want to ignore text use
Excel Formula:
``=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)``

Replies
0
Views
165
Replies
15
Views
209
Replies
4
Views
104
Replies
3
Views
111
Replies
1
Views
78

1,148,327
Messages
5,746,128
Members
423,994
Latest member
blzxatly

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back