Highlight consecutive cells in a row and collunm from table

[Jorge Correia]

Hello,

I am trying to highlight the value "0" with conditional formating in consecutives cells (in the table) in the same row and in the same colunm like I done manualy in picture b3a.
The first value in the table is in cell B3.
I have this formula ="and(or((B3=D3,C3=b3),B3=0),or((E3=D3,F3=D3),b3=0)" but there something not working...
I know it's possible in a row but is it like I am trying?
Thank you.

 

[Joe4]

Welcome to the Board!

You would also need to check the row above and the row below, i.e. for cell B3:

=AND(B3=0,OR(B2=0,B4=0,A3=0,C3=0))

 

[Jorge Correia]

Welcome to the Board!

You would also need to check the row above and the row below, i.e. for cell B3:

=AND(B3=0,OR(B2=0,B4=0,A3=0,C3=0))

Thank you.
I have this error with your formula, I had it also with others.
My table has data from $A$1:$BL$500+

 

[Joe4]

It looks like you first data entry is in cell B2.
So, select the range B2:BL500, and go to Conditional Formatting, and enter this formula:

=AND(B2=0,B2<>"",OR(AND(A2=0,A2<>""),AND(C2=0,C2<>""),AND(B1=0,B1<>""),AND(B3=0,B3<>"")))

and choose your formatting option.

That seemed to work for me.

 

[Jorge Correia]

It looks like you first data entry is in cell B2.
So, select the range B2:BL500, and go to Conditional Formatting, and enter this formula:

=AND(B2=0,B2<>"",OR(AND(A2=0,A2<>""),AND(C2=0,C2<>""),AND(B1=0,B1<>""),AND(B3=0,B3<>"")))

and choose your formatting option.

That seemed to work for me.

I always got this error.
I got Office Professional Plus 2016.
The formula can be right, I tried to modify to avoid the error but It pop up every time.

 

[Fluff]

Try changing all the commas to semi-colons.

 

[Jorge Correia]

Try changing all the commas to semi-colons.

Thanks, the error is gone but the formula don't highlight anything.
I want to highlight only the consecutive cells in a row and in a collunm that have the value "0" (zero), like I did manually in the picture.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jorge Correia]

I always got this error.
I got Office Professional Plus 2016.
The formula can be right, I tried to modify to avoid the error but It pop up every time.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

consecutive value.xlsm
	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	AG	AH	AI	AJ
3	45	4	38	7	19	3	18	2	2	3	14	6	5	5	1	5	2	3	6	1	0	10	2	0	0	5	4	41	1	6	16	15	1	14	12
4	46	5	39	8	20	4	19	3	0	4	0	7	6	6	2	6	3	4	7	2	1	11	3	1	0	6	5	42	2	7	17	16	2	15	13
5	47	0	40	9	21	5	20	4	1	5	1	8	0	0	3	7	4	5	8	3	2	12	4	0	1	7	6	43	3	8	18	17	3	16	14
6	48	1	41	0	22	6	21	5	2	0	2	9	1	1	4	0	0	6	9	4	3	13	5	1	2	8	7	44	4	9	19	18	0	17	15
7	49	2	42	1	23	7	22	6	3	1	3	10	2	2	5	0	1	7	10	5	4	14	6	2	3	9	8	45	5	10	20	19	1	18	16
8	50	3	43	2	0	8	23	7	4	2	4	11	3	3	6	1	2	0	11	6	5	15	7	3	4	10	9	46	6	11	21	20	2	0	0
9	51	4	44	0	1	9	24	8	5	3	5	12	4	4	7	2	3	1	12	0	6	16	8	0	5	11	10	0	0	12	22	21	3	1	1
10	52	5	45	1	2	10	25	0	6	4	6	13	0	0	8	3	4	2	13	1	7	17	9	1	6	12	0	1	1	13	23	22	4	2	2
11	53	6	46	2	3	11	26	1	7	5	7	14	0	1	9	4	5	3	14	2	8	18	10	0	7	13	1	2	2	14	24	0	5	3	0
12	54	7	47	3	4	12	27	2	8	0	8	15	1	2	10	5	6	4	15	3	9	19	0	1	8	14	2	0	3	15	25	1	6	4	1
13	55	8	48	4	5	13	28	3	9	1	9	16	2	3	11	0	7	5	16	4	10	20	1	2	9	15	0	1	4	16	26	2	7	5	2
14	56	9	49	5	6	14	29	4	10	2	10	17	3	0	12	1	8	6	17	5	11	21	2	3	10	0	1	2	5	17	27	3	8	6	3
15	57	10	50	6	0	15	30	0	11	3	0	18	4	1	13	2	9	7	18	6	12	22	3	4	11	1	2	3	6	18	28	4	9	7	4
16	0	11	51	7	0	16	31	1	12	4	1	0	5	2	14	0	10	8	19	7	13	23	4	5	12	2	3	4	7	19	29	5	10	8	5
17	0	12	52	0	1	17	32	2	0	5	2	1	6	3	15	0	11	9	20	8	0	24	5	6	13	3	4	5	8	20	30	6	11	9	6
18	1	13	53	1	2	18	33	3	1	6	3	0	7	4	16	1	12	10	21	9	1	25	6	7	14	0	5	6	9	21	31	7	12	10	7
19	2	14	54	2	3	19	34	4	2	7	4	1	8	5	17	2	0	11	22	10	2	26	7	8	15	1	6	7	10	22	0	8	13	11	0
zero

Cell Formulas
Range	Formula
AG19:AI19,S19:AE19,B19:Q19,N18:Z18,B18:L18,R17:U17,K17:P17,F17:I17,C17:D17,N16:P16,G16:L16,W15:AA17,R15:V16,M15:Q15,J15:K15,G15:H15,C15:E16,B4:B15,AB14:AJ18,P14:Z14,C14:N14,AC13:AJ13,R13:AA13,C13:P13,AD12:AJ12,Y12:AB12,L12:W12,C12:J12,AH11:AI11,Z11:AF11	C4	=C3+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AT228	Cell Value	=0	text	NO
B3:AT228	Expression	=AND(B2=0;B2<>"";OR(AND(A2=0;A2<>"");AND(C2=0;C2<>"");AND(B1=0;B1<>"");AND(B3=0;B3<>"")))	text	NO

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	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	AG	AH	AI	AJ
3	45	4	38	7	19	3	18	2	2	3	14	6	5	5	1	5	2	3	6	1	0	10	2	0	0	5	4	41	1	6	16	15	1	14	12
4	46	5	39	8	20	4	19	3	0	4	0	7	6	6	2	6	3	4	7	2	1	11	3	1	0	6	5	42	2	7	17	16	2	15	13
5	47	0	40	9	21	5	20	4	1	5	1	8	0	0	3	7	4	5	8	3	2	12	4	0	1	7	6	43	3	8	18	17	3	16	14
6	48	1	41	0	22	6	21	5	2	0	2	9	1	1	4	0	0	6	9	4	3	13	5	1	2	8	7	44	4	9	19	18	0	17	15
7	49	2	42	1	23	7	22	6	3	1	3	10	2	2	5	0	1	7	10	5	4	14	6	2	3	9	8	45	5	10	20	19	1	18	16
8	50	3	43	2	0	8	23	7	4	2	4	11	3	3	6	1	2	0	11	6	5	15	7	3	4	10	9	46	6	11	21	20	2	0	0
9	51	4	44	0	1	9	24	8	5	3	5	12	4	4	7	2	3	1	12	0	6	16	8	0	5	11	10	0	0	12	22	21	3	1	1
10	52	5	45	1	2	10	25	0	6	4	6	13	0	0	8	3	4	2	13	1	7	17	9	1	6	12	0	1	1	13	23	22	4	2	2
11	53	6	46	2	3	11	26	1	7	5	7	14	0	1	9	4	5	3	14	2	8	18	10	0	7	13	1	2	2	14	24	0	5	3	0
12	54	7	47	3	4	12	27	2	8	0	8	15	1	2	10	5	6	4	15	3	9	19	0	1	8	14	2	0	3	15	25	1	6	4	1
13	55	8	48	4	5	13	28	3	9	1	9	16	2	3	11	0	7	5	16	4	10	20	1	2	9	15	0	1	4	16	26	2	7	5	2
14	56	9	49	5	6	14	29	4	10	2	10	17	3	0	12	1	8	6	17	5	11	21	2	3	10	0	1	2	5	17	27	3	8	6	3
15	57	10	50	6	0	15	30	0	11	3	0	18	4	1	13	2	9	7	18	6	12	22	3	4	11	1	2	3	6	18	28	4	9	7	4
16	0	11	51	7	0	16	31	1	12	4	1	0	5	2	14	0	10	8	19	7	13	23	4	5	12	2	3	4	7	19	29	5	10	8	5
17	0	12	52	0	1	17	32	2	0	5	2	1	6	3	15	0	11	9	20	8	0	24	5	6	13	3	4	5	8	20	30	6	11	9	6
18	1	13	53	1	2	18	33	3	1	6	3	0	7	4	16	1	12	10	21	9	1	25	6	7	14	0	5	6	9	21	31	7	12	10	7
19	2	14	54	2	3	19	34	4	2	7	4	1	8	5	17	2	0	11	22	10	2	26	7	8	15	1	6	7	10	22	0	8	13	11	0
Main

Cell Formulas
Range	Formula
C4:I4,AG19:AI19,S19:AE19,B19:Q19,N18:Z18,B18:L18,R17:U17,K17:P17,F17:I17,C17:D17,N16:P16,G16:L16,W15:AA17,R15:V16,M15:Q15,J15:K15,G15:H15,C15:E16,B4:B15,AB14:AJ18,P14:Z14,C14:N14,AC13:AJ13,R13:AA13,C13:P13,AD12:AJ12,Y12:AB12,L12:W12,C12:J12,AH11:AI11	C4	=C3+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AJ19	Expression	=AND(B3=0,B3<>"",OR(AND(A3=0,A3<>""),AND(C3=0,C3<>""),AND(B2=0,B2<>""),AND(B4=0,B4<>"")))	text	NO

Once again you will need to change the comma to semi-colon