Solution for Excel 2016 number of consecutive cells in row = 3 does not quite work

[Gregory123987]

I had needed a solution to automatically provide conditional formatting if there were consecutive days with values. Unfortunately, the solution that was found did not highlight the 3rd consecutive cell for the current month if there were more than 2 cells ending the previous month. The 9 hidden columns to the left of column M have a value if column J (previous month) has a value.

The same issue applies for 5 and 10 cells as well, but I can fix this myself if someone can help with a solution for the 3rd consecutive cell issue I just described. A link to the previous thread is below.

FYI - I did not do a proper job of testing it as time was of the essence. It has been a year since then and I noticed the issue as I was making changes to the spreadsheet. Thank you in advance for your help.

Spoiler: Previous solution

It took me a while but I managed to figure out a solution. Here it for your info. Thanks for your help.

Added hidden columns, View attachment 19961

Expanded conditional formatting to include applicable columns, wrapped Column with And and added cell reference such as the following to change from
=COLUMN()=(MIN(IF(($M18:$AG18<>"")*($M18:$AH18<>"")*($M18:$AI18<>""),COLUMN($M$16:$AG$16)+2))) to
=AND($J18<3,(COLUMN()=(MIN(IF(($T18:$AP18<>"")*($U18:$AQ18<>"")*($V18:$AR18<>""),COLUMN($T$16:$AP$16)+2)))))

 

[jasonb75]

I haven't done a lot of testing but think that this is on the right track. The hidden columns W:AE are not needed for this. The number of days from the previous month is taken directly from column J (copied to BG in the example for easier visual reference).

The formulas for Blue, Yellow, and Orange are identical apart from the 3, 5, or 10 at the very end.

Manexcel.xlsx
	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG
22																												Column J
23	1	1	1	1	1	1	1	1	1	1	1	1	1						1	1	1	1	1	1	1	1		0
24	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		0
25	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		2
26	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		3
27	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		4
28	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		9
29	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		31
Sheet11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF23:BE34	Expression	=IF(AF23="","",IF(COUNTBLANK($AF23:AF23),IF(COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23)))>$J23+COUNT(OFFSET($AF23,,,,MATCH(TRUE,$AF23:AF23="",0))),COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23))),""),MOD(IF($J23<10,$J23)+COUNT($AF23:AF23)-1,IF(COLUMNS($AF3:AF3)<10,10,1E+100))+1))=10	text	NO
AF23:BE34	Expression	=IF(AF23="","",IF(COUNTBLANK($AF23:AF23),IF(COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23)))>$J23+COUNT(OFFSET($AF23,,,,MATCH(TRUE,$AF23:AF23="",0))),COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23))),""),MOD(IF($J23<10,$J23)+COUNT($AF23:AF23)-1,IF(COLUMNS($AF3:AF3)<10,10,1E+100))+1))=5	text	NO
AF23:BE34	Expression	=IF(AF23="","",IF(COUNTBLANK($AF23:AF23),IF(COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23)))>$J23+COUNT(OFFSET($AF23,,,,MATCH(TRUE,$AF23:AF23="",0))),COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23))),""),MOD(IF($J23<10,$J23)+COUNT($AF23:AF23)-1,IF(COLUMNS($AF3:AF3)<10,10,1E+100))+1))=3	text	NO
AF23:BE34	Expression	=AND(AF23<>"",($J23+COUNT(AF23:$AF23))=1)	text	NO

 

[Gregory123987]

Wow. this is elegant. I just posted the conditional formulas and it all seems to work. Except for the red.
=AND(AF23<>"",($J23+COUNT(AF23:$AF23))=1) | changed to =AND(AF23<>"",($J23+COUNT(AF23:AF23))=1) Removed the last $, and that seemed to work.

 

[Gregory123987]

Hmmm... Looks like I was wrong.

It all seems to work. Thanks, @jasonb75 for your help.

 

[Gregory123987]

@jasonb75, just to be sure, part of the formula seems to be incorrect, could you confirm? It seems to be outside the range of the worksheet example.

=IF(AF23="","",IF(COUNTBLANK($AF23:AF23),IF(COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23)))>$J23+COUNT(OFFSET($AF23,,,,MATCH(TRUE,$AF23:AF23="",0))),COUNT(OFFSET(AF23,,,,MATCH(2,1/($AF23:AF23=""))-COLUMNS($AF23:AF23))),""),MOD(IF($J23<10,$J23)+COUNT($AF23:AF23)-1,IF(COLUMNS($AF3:AF3)<10,10,1E+100))+1))=3

Should this be $AF23:AF23

EDIT: PS I used the Yellow, but it is applicable to all 3.

 

[jasonb75]

Should this be $AF23:AF23

Ideally, yes. It is not essential but I would recommend correcting it to avoid errors later.

The COLUMNS() function only serves as a counter, as long as it starts with a single cell, the $ symbol is correctly placed, and you don't insert / delete any rows / cells / columns then it will be fine.
Inserting / deleting can cause errors depending on where it is done relative to the position of the formula cells.