How can I set up conditional formatting to highlight a range only if all its cells are empty?

Question

I am new to conditional formatting and having a hard time. I have 6 columns with 100 rows. What I would like to have happen is to highlight the row in one color if there is no data in it at all. If there is data in one cell within the row, however, I would like for the highlighting to be removed from the row completely. Currently I have it set up to highlight the entire row if there is no data in it and if there is data in one cell, only that cell has no highlighting....I can't seem to make the entire row's highlighting disappear.

I have used the formula to determine which cells to format:

=I16:N16="" formatting color is yellow.

I know I have to add a second conditional format but I have tried numerous different formulas and cant seem to get it to work.

Answer 1

Assuming data starts at row 16 then select the whole range (I16:N115?) and use this formula in conditional formatting

=COUNTIF($I16:$N16,"<>")=0

The $ signs are required as shown

Note: this formula should also work

=AND($I16:$N16="")

....but that's an example of an "array formula" which Excel sometimes mismanages in conditional formatting (particularly Excel 2007 I believe) so I recommend the above version with COUNTIF

Answer 2

For only six columns, you can check each cell individually. Assuming your data starts at I16, this should do the trick:

=NOT(AND($I16="", $J16="", $K16="", $L16="", $M16="", $N16=""))

This will return true for those rows where all six cells (columns A-F) are empty.

See the documentation on the AND() and NOT() functions for more information.