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

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[6StringJazzer]

This can be done with conditional formatting.

$scratch.xlsm
	A	B	C	D	E
1	DATE	3-Jan	4-Jan	5-Jan	6-Jan
2	AKLUJ	0	200.64	0	0
3	AMBAJOGAI (BEED-DISTRICT)	0	0	0	0
4	BAGALKOT	99.82	0	0	189.46
5	BANGARPET	482.82	1540.88	129.03	922.3
6	BG KERE	1690.97	116.21	648.27	177.15
7	CHANNARAYAPATNA	0	429.96	0	0
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E7	Expression	=AND(B2=0,OR(A2=0,C2=0))	text	NO
B2:E7	Expression	=B2=0	text	NO

 

[etaf]

i used a countif() in normal conditional formatting

Book1
	A	B	C	D	E	F
1	DATE	44564	44565	44566	44567	44568
2	AKLUJ	0	200.64	0	0	764.09
3	AMBAJOGAI (BEED-DISTRICT)	0	648.27	177.15	648.27	177.15
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	648.27	177.15	1144.99
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F7	Expression	=COUNTIF($B2:$F2,0)>1	text	NO
B2:F7	Expression	=COUNTIF($B2:$F2,0)=1	text	NO

 

[Peter_SSs]

This can be done with conditional formatting.

I agree, but if your interpretation of the requirement is correct, I think a different formula for red would most likely be required.
Try changing cell E5 in your layout to 0.

If MS365 is available, this would be one way.

22 02 07.xlsm
	B	C	D	E	F
2	0	200.64	0	0	764.09
3	0	0	0	0	0
4	99.82	0	0	189.46	779.07
5	482.82	1540.88	129.03	922.3	1267.17
6	1690.97	116.21	648.27	177.15	931.53
7	0	429.96	0	0	1144.99
8	5	5	5	5	0
CF0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F8	Expression	=LET(t,CONCAT(1,IF($B2:$F2=0,0,1)),OR(MID(t,COLUMNS($B:B),2)="00",MID(t,COLUMNS($B:B)+1,2)="00"))	text	NO
B2:F8	Expression	=B2=0	text	NO

 

[6StringJazzer]

I agree, but if your interpretation of the requirement is correct, I think a different formula for red would most likely be required.

Why?

 

[Peter_SSs]

Why?

Try changing cell E5 in your layout to 0.

22 02 07.xlsm
	A	B	C	D	E
1	DATE	1-Mar	1-Apr	1-May	1-Jun
2	AKLUJ	0	200.64	0	0
3	AMBAJOGAI (BEED-DISTRICT)	0	0	0	0
4	BAGALKOT	99.82	0	0	189.46
5	BANGARPET	482.82	1540.88	129.03	0
6	BG KERE	1690.97	116.21	648.27	177.15
7	CHANNARAYAPATNA	0	429.96	0	0
CF Test

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E7	Expression	=AND(B2=0,OR(A2=0,C2=0))	text	NO
B2:E7	Expression	=B2=0	text	NO

 

[6StringJazzer]

Thank you. Minor tweak to omit considering blank cells.

$scratch.xlsm
	A	B	C	D	E
1	DATE	3-Jan	4-Jan	5-Jan	6-Jan
2	AKLUJ	0	200.64	0	0
3	AMBAJOGAI (BEED-DISTRICT)	0	0	0	0
4	BAGALKOT	99.82	0	0	189.46
5	BANGARPET	482.82	1540.88	129.03	0
6	BG KERE	1690.97	116.21	648.27	177.15
7	CHANNARAYAPATNA	0	429.96	0	0
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E7	Expression	=AND(AND(B2<>"",B2=0),OR(AND(A2<>"",A2=0),AND(C2<>"",C2=0)))	text	NO
B2:E7	Expression	=B2=0	text	NO

 

[Peter_SSs]

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

 

[6StringJazzer]

If our interpretation of the requirement is the correct one then here is a much simpler way

Very clever