Conditional Formatting

[Dhinakaran]

Hi,

From the below table, we have a 0(Zero's) repeated on multiple occasions and I wish to highlight the same basis the below conditions.

1. One instance of 0 should be highlighted in Yellow (under rows).
2. More than 1 instance of consecutive 0's to be highlighted in Red (under rows).

However the same couldn't be done using the conditional formatting, Hence seek assistance for the same.

Book1
	A	B	C	D	E	F
1	DATE	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan
2	AKLUJ	0	200.64	0	0	764.09
3	AMBAJOGAI (BEED-DISTRICT)	0	0	0	0	0
4	BAGALKOT	99.82	0	0	189.46	779.07
5	BANGARPET	482.82	1540.88	129.03	922.3	1267.17
6	BG KERE	1690.97	116.21	648.27	177.15	931.53
7	CHANNARAYAPATNA	0	429.96	0	0	1144.99
Sheet1

 

[Dhinakaran]

If our interpretation of the requirement is the correct one then here is a much simpler way than my previous one, and also does not require MS365.
Also a check in case a single cell happens to be blank, not zero.

22 02 07.xlsm
	A	B	C	D	E	F
1	DATE	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan
2	AKLUJ	0	200.64	0	0	764.09
3	AMBAJOGAI (BEED-DISTRICT)	0	0	0	0	0
4	BAGALKOT	99.82	0	0	189.46	779.07
5	BANGARPET	482.82	1540.88	129.03	922.3	1267.17
6	BG KERE	1690.97	116.21	648.27	177.15	931.53
7	CHANNARAYAPATNA	0	429.96	0	0	1144.99
8	ABC	5		5	5	0
CF0 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F8	Expression	=FIND("|0|0|","|"&A2&"|"&B2&"|"&C2&"|")	text	NO
B2:F8	Expression	=B2&""="0"	text	NO

Hi Peter,

Thanks for your solution. Did imply the same in my data, However the there seems to be some error as cell H4, K4, F2 and H7 are incorrectly highlighted. Not sure if i'm missing something. Please guide.

Conditional format sample.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
1	#	Manager	Zone	Center	01-Feb	02-Feb	03-Feb	04-Feb	05-Feb	06-Feb	07-Feb	08-Feb	09-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb
2		Total
3		SubTotal
4	1	John	West	CA	1	0	0	0	1	0	1	0	0	1	1	0	0	0	1	0	1	0	0	1	1	0	0	0	1	0	1	0
5	2	David	South	LA	0	1	0	1	0	1	0	1	1	0	0	1	0	1	0	1	0	1	1	0	0	1	0	1	0	1	0	1
6	3	John	West	CA	0	0	1	1	0	1	0	0	1	1	0	0	1	1	0	1	0	0	1	1	0	0	1	1	0	1	0	0
7	4	Christ	North	PH	1	0	0	0	1	0	1	1	0	0	1	0	0	0	1	0	1	1	0	0	1	0	0	0	1	0	1	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:AF7	Expression	=FIND("|0|0|","|"&E4&"|"&F4&"|"&G4&"|")	text	NO
E4:AF7	Expression	=E4&""="0"	text	NO

 

[Peter_SSs]

You are referencing the wrong cells with the 'red' formula. The first one should reference D4, E4 & F4

22 02 07.xlsm
	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
4	1	John	West	CA	1	0	0	0	1	0	1	0	0	1	1	0	0	0	1	0	1	0	0	1	1	0	0	0	1	0	1	0
5	2	David	South	LA	0	1	0	1	0	1	0	1	1	0	0	1	0	1	0	1	0	1	1	0	0	1	0	1	0	1	0	1
6	3	John	West	CA	0	0	1	1	0	1	0	0	1	1	0	0	1	1	0	1	0	0	1	1	0	0	1	1	0	1	0	0
7	4	Christ	North	PH	1	0	0	0	1	0	1	1	0	0	1	0	0	0	1	0	1	1	0	0	1	0	0	0	1	0	1	1
CF0 (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:AF7	Expression	=FIND("|0|0|","|"&D4&"|"&E4&"|"&F4&"|")	text	NO
E4:AF7	Expression	=E4&""="0"	text	NO

 

[Dhinakaran]

Thanks much Peter

 

[Peter_SSs]

You're welcome.