Conditional formatting formula to highlight a row that contains all empty cell

[nsa1]

Hello,

How to set Conditional Formatting and use formula to have excel highlight a row of certain cells in a row have all blank values.

thanks,
NA

 

[DanteAmor]

Try:

Dante Amor
	A	B	C	D
1		value	value	value
2	u
3	v	1
4	w		1
5	x			1
6	y
7	z	1	1	1
Hoja5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D7	Expression	=COUNTA($B2:$D2)=0	text	NO

Please don't forget the feedback in this thread:

Why is do Conditional formatting with rule of format only cells that contain "~"

Hello, Add a cell with xyxyxy~. Then add a conditional formatting rule that says Format only cells that contains ~ by... In the Edit the Rule Description: Format only cells with "Specific Text", "Containing", ~ Format to have that Cell Filled in with some color Then OK and Apply... This above...

www.mrexcel.com

 

[nsa1]

Try:

Dante Amor
	A	B	C	D
1		value	value	value
2	u
3	v	1
4	w		1
5	x			1
6	y
7	z	1	1	1
Hoja5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D7	Expression	=COUNTA($B2:$D2)=0	text	NO

Please don't forget the feedback in this thread:

Why is do Conditional formatting with rule of format only cells that contain "~"

Hello, Add a cell with xyxyxy~. Then add a conditional formatting rule that says Format only cells that contains ~ by... In the Edit the Rule Description: Format only cells with "Specific Text", "Containing", ~ Format to have that Cell Filled in with some color Then OK and Apply... This above...

www.mrexcel.com

Thanks!

 

[nsa1]

Thanks!

I tried and worked in the example spreadsheet. But, somehow the same setup in my original spreadsheet...it does not work? There is no difference...only difference is that there is a text and not number in the cell. I want to highlight a cell from B2:B7 if all cell in the rows are empty

 

[awoohaw]

I tried and worked in the example spreadsheet. But, somehow the same setup in my original spreadsheet...it does not work? There is no difference...only difference is that there is a text and not number in the cell. I want to highlight a cell from B2:B7 if all cell in the rows are empty

maybe you can post a sample of your data using the xl2bb add in (link below) so we can see what you are working with?
Thanks in advance.

 

[nsa1]

I can't install xl2bb for me to upload mini-sheet. I tried to follow steps to install it but can't find any add ins that is xl2bb. I can't download anything from outside

 

[DanteAmor]

But, somehow the same setup in my original spreadsheet...it does not work? There is no difference...only difference is that there is a text and not number in the cell.

Do you have formulas in cells?
Try the following formula:

=COUNTIF($B2:$D2,"")=3

Dante Amor
	A	B	C	D
1		value	value	value
2	u
3	v	x
4	w			x
5	xd
6	y		x
7	z	x	x	x
Hoja5

Cell Formulas
Range	Formula
B2:D7	B2	=IF(E2="","","x")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D7	Expression	=COUNTIF($B2:$D2,"")=3	text	NO

I share my file with the 2 options so that you can see how you should put the conditional format.
conditional format count

 

[nsa1]

Same problem it highlights all of them. Yes, those Col B to C have other formatted rules that will highlight them in some color and text in white based on the last character.

 

[DanteAmor]

But you must be specific with what you need.

In your image of the initial post you have 3 cells to verify.
That's why the formula in post #7 says equal to 3

=COUNTIF($B2:$D2,"")=3

But now your image has 7 cells.
So the formula should be:

=COUNTIF($B2:$D2,"")=7

But if you only come here with "it does not work... Same problem" it's not much help.

You must expose from the beginning what you have in your cells and what you have on your sheet with clear and real examples, in this way we will provide you with solutions according to your problem, otherwise we will be going around and you will always have problems with our solutions.

 

[nsa1]

Thanks...I just didn't understand the countif function output well..it counts # of blanks ""...so use 7...got it...it worked!