Conditional Formatting to Highlight Items

[Campbellmnc]

I am trying to figure out a conditional format that after seven days of no change would highlight. I would need to highlight any consecutive 7 blank cells.
Sample data

1/1​

1/2​

1/3​

1/4​

1/5​

1/6​

1/7​

1/8​

1/9​

1/10​

1/11​

1/12​

1/13​

1/14​

1/15​

1/16​

1/17​

1/18​

1/19​

1/20​

1/21​

1/22​

1/23​

1/24​

1/25​

1/26​

1/26​

1/27​

1/28​

1/29​

1/30​

1/31​

Veggie Item

Mexico

Mexico

Mexico

Mexico

Mexico

Mexico

 

[pjmorris]

	01/01	01/02	01/03	01/04	01/05	01/06	01/07	01/08	01/09	01/10	01/11	01/12	01/13	01/14	01/15	01/16	01/17	01/18	01/19	01/20	01/21	01/22	01/23	01/24	01/25	01/26	01/27	01/28	01/29	01/30	01/31
Veggie Item							Mexico			Mexico				Mexico					Mexico								Mexico	Mexico

The conditional format formula is =COUNTBLANK(B2:H2)=7 and the conditional formatting starts in cell H2 and extends to the right as far as necessary. It is not worth trying to format the preceding cells as they cannot have sufficient blank cells amongst them to trigger the formatting. Only cell AA meets the criteria.

HTH

 

[JamesCanale]

MrExcelPlayground15.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	Y	Z	AA	AB	AC	AD	AE	AF
2		1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	16-Jan	17-Jan	18-Jan	19-Jan	20-Jan	21-Jan	22-Jan	23-Jan	24-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan	30-Jan	31-Jan
3	Veggie Item							Mexico			Mexico				Mexico					Mexico									Mexico	Mexico
4
5		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
Sheet26

Cell Formulas
Range	Formula
C2:AF2	C2	=B2+1
B5	B5	=OR(AND(OFFSET(B3,0,0,1,7)=0))
C5	C5	=OR(AND(OFFSET(C3,0,-2,1,7)=0),AND(OFFSET(C3,0,-1,1,7)=0),AND(OFFSET(C3,0,0,1,7)=0))
D5	D5	=OR(AND(OFFSET(D3,0,-3,1,7)=0),AND(OFFSET(D3,0,-2,1,7)=0),AND(OFFSET(D3,0,-1,1,7)=0),AND(OFFSET(D3,0,0,1,7)=0))
E5	E5	=OR(AND(OFFSET(E3,0,-4,1,7)=0),AND(OFFSET(E3,0,-3,1,7)=0),AND(OFFSET(E3,0,-2,1,7)=0),AND(OFFSET(E3,0,-1,1,7)=0),AND(OFFSET(E3,0,0,1,7)=0))
F5	F5	=OR(AND(OFFSET(F3,0,-5,1,7)=0),AND(OFFSET(F3,0,-4,1,7)=0),AND(OFFSET(F3,0,-3,1,7)=0),AND(OFFSET(F3,0,-2,1,7)=0),AND(OFFSET(F3,0,-1,1,7)=0),AND(OFFSET(F3,0,0,1,7)=0))
G5:AF5	G5	=OR(AND(OFFSET(G3,0,-6,1,7)=0),AND(OFFSET(G3,0,-5,1,7)=0),AND(OFFSET(G3,0,-4,1,7)=0),AND(OFFSET(G3,0,-3,1,7)=0),AND(OFFSET(G3,0,-2,1,7)=0),AND(OFFSET(G3,0,-1,1,7)=0),AND(OFFSET(G3,0,0,1,7)=0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	Expression	=OR(AND(OFFSET(B3,0,0,1,7)=0))	text	NO
C3	Expression	=OR(AND(OFFSET(C3,0,-2,1,7)=0),AND(OFFSET(C3,0,-1,1,7)=0),AND(OFFSET(C3,0,0,1,7)=0))	text	NO
D3	Expression	=OR(AND(OFFSET(D3,0,-3,1,7)=0),AND(OFFSET(D3,0,-2,1,7)=0),AND(OFFSET(D3,0,-1,1,7)=0),AND(OFFSET(D3,0,0,1,7)=0))	text	NO
E3	Expression	=OR(AND(OFFSET(E3,0,-4,1,7)=0),AND(OFFSET(E3,0,-3,1,7)=0),AND(OFFSET(E3,0,-2,1,7)=0),AND(OFFSET(E3,0,-1,1,7)=0),AND(OFFSET(E3,0,0,1,7)=0))	text	NO
F3	Expression	=OR(AND(OFFSET(F3,0,-5,1,7)=0),AND(OFFSET(F3,0,-4,1,7)=0),AND(OFFSET(F3,0,-3,1,7)=0),AND(OFFSET(F3,0,-2,1,7)=0),AND(OFFSET(F3,0,-1,1,7)=0),AND(OFFSET(F3,0,0,1,7)=0))	text	NO
G3:AF3	Expression	=OR(AND(OFFSET(G3,0,-6,1,7)=0),AND(OFFSET(G3,0,-5,1,7)=0),AND(OFFSET(G3,0,-4,1,7)=0),AND(OFFSET(G3,0,-3,1,7)=0),AND(OFFSET(G3,0,-2,1,7)=0),AND(OFFSET(G3,0,-1,1,7)=0),AND(OFFSET(G3,0,0,1,7)=0))	text	NO

 

[Campbellmnc]

How you would set this up if there were about 20 rows with different items?

 

[pjmorris]

Assuming it is working on the single row, simply go to the Conditional Formatting Rules Manager and extend the 'applies to' range. (it should be as easy as that).

 

[Campbellmnc]

Assuming it is working on the single row, simply go to the Conditional Formatting Rules Manager and extend the 'applies to' range. (it should be as easy as that).

Thank you.

 

[pjmorris]

delighted to have helped - if it has solved the problem please mark it as an answer as that will help others in the future.

All the best.