0

I'd like to set up conditional formatting in columns B:D such that blank cells in B:D are highlighted but only when the same row in A is NOT empty.

I think the custom formula should be something like this, but it's not working...

=and(not(isblank(A:A)),isblank($B:$D))

enter image description here

0

You got pretty close with your idea about ISBLANK.

Use this as the Custom formula applying to Columns B to D

=AND(not(isblank($A:$A)),isblank($B1:$D1))

The formula evaluates two ranges:
1) cell A = whether it is NOT empty
2) cell B or C or D = whether they are blank (that's the impact of the absolute column reference).
The criterion are joined with AND. So the net effect is as if to say:

If cell A is NOT empty, then apply the formatting to the cells in Column B, C, and D UNLESS the respective cell B, C or D is not empty in which case don't apply the formatting to that non-empty cell.

This is the screenshot of the indicative outcome. enter image description here

Credit:
Absolute Vs Relatives references in critera: Zig Mandel and tehhowch
Formatting Rule applying to a column: Ed Nelson

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.