I'm looking for how to apply conditional formatting to blank cells - but ONLY if the row they are in is not completely blank.

For example, I'm making a project tracking sheet, and I want incomplete stages in projects to stand out. So here is how it looks with a simple "format blank cells" rule applied to the first four rows:

enter image description here

What I want is, as soon as I enter content into the new row, the conditional formatting to apply, like this:

enter image description here

...but I don't want the conditional formatting to apply to every blank row like this, I only want it to apply to rows that have some content. I don't want a big sea of red, I want red only where there is a task that needs doing:

enter image description here

If it's relevant, I'm using the Excel 2016 trial.

up vote 1 down vote accepted

Create a new Conditional Formatting rule that applies to =$A:$G and use the following formula. Both Gs can be replaced with which ever end column you need.

=AND(COUNTA($A1:$G1)>0, ISBLANK(A1))

enter image description here

  • Worked perfectly! Just to be clear, the A1 in the isblank should be the first cell in my selection? – user568458 Nov 3 '15 at 16:20
  • 1
    I am not really sure how to explain it, basically A1 is kind of like an offset(?) where A is 0 and 1 is 0. When the rule applies to A1 it is A1, when it applies to Z5 it is Z5.If you only want formatting to apply to rows where the first cell is NOT empty you could use the following =AND(NOT(ISBLANK($A1)), ISBLANK(A1)). Then any Cell that is blank in a row where the A column is not empty is highlighted. You can add any other row checks you need to the main and, any Column/Row not preceeded by a $ is incremented based on the current cell the function is checking. – Marie Nov 3 '15 at 16:30

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.