CODEWITHSUNDEEP
excelconditional-formatting
Robby
Robby

Reputation: 827

How to create condition "If cell is blank and other cells in same row are not blank"?

I need cell A1 to be highlighted if it's blank AND any cell in the same row is NOT blank. Most of the cells in the row will have formulas in them.

Upvotes: 1

Views: 6462

Answers (3)

Mike
Mike

Reputation: 1

=(G1="")*SUMPRODUCT((1:1<>"")+0)

Works, for column G being blank when row has another cell populated.

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

Enter this formula in Conditional Formatting:

=(A1="")*COUNTA(1:1)

The above will count blanks from a formula as not being truly blank. If you wish for the Conditional Formatting to ignore these types of blanks then you can use this formula instead:

=(A1="")*SUMPRODUCT((1:1<>"")+0)

Upvotes: 3

BruceWayne
BruceWayne

Reputation: 23283

Create a new conditional format, and use this formula:

=AND($A$1="",COUNTA(1:1)>=1)

and then "Format" -> "Fill" and choose your color.

When A1 is NOT blank, there's no fill in.

When A1 is blank, AND there's a value in the same row somewhere, there will be fill in.

When A1 is blank, and the rest of the row is blank too, no fill.

Upvotes: 3

Related Questions