How do you highlight an entire row if all values are the same

[LeighDIFonzo]

I have a large spreadsheet, and have 24 specific columns with values starting at 0. I need to highlight the entire row if all 24 columns have a value of 0. There has to be a way to do this... I don't need to highlight the row if any of the 24 columns has a value other than 0.

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	TRUE
3
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet9

Cell Formulas
Range	Formula
A2	A2	=SUM(--($A4:$X4=0))=24
A4:X4	A4	=SEQUENCE(1,24,0,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:X6	Expression	=SUM(--(A4:X4=0))=24	text	NO

 

[LeighDIFonzo]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	TRUE
3
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet9

Cell Formulas
Range	Formula
A2	A2	=SUM(--($A4:$X4=0))=24
A4:X4	A4	=SEQUENCE(1,24,0,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:X6	Expression	=SUM(--(A4:X4=0))=24	text	NO

I should have mentioned I have 1100+ lines of data, would I do this as conditional formatting or is there a formula?

 

[awoohaw]

I conditionally formatted the first row,
Then Copied the formatting over all the rows (including the first row).

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not sure just what your data could be like but I don't think the the above suggestion is robust.

Here is that CF formula with some different sample data.

• All row 5 is formatted when not all values are zero.
• Much of row 7 is not formatted even though all 24 values are zero.
• Some of row 8 is formatted even though not all 24 values are zero.

24 02 18.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
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0																							0
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		33
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	2	0	0	0
CF1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z7,A4:X8	Expression	=SUM(--(A4:X4=0))=24	text	NO

The rows 7 & 8 issues would be resolved if $ signs were used in the CF formula but the row 5 issue would remain.

24 02 18.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
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0																							0
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		33
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	2	0	0	0
CF2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:X8	Expression	=SUM(--($A4:$X4=0))=24	text	NO

I would use this to be more robust

24 02 18.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
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0																							0
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		33
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	2	0	0	0
CF3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:X8	Expression	=COUNTIF($A4:$X4,0)=24	text	NO