Conditional Formatting - need to highlight blank cells in a range IF two cells are correct

Hi all,

I'm trying to sort out a conditional formatting problem. Please see attached workbook for the file example.

What I'm trying to do is basically show if a line in our data entry is incomplete. This is determined by the value of two cells at the end of the row.

The conditional formatting needs to highlight the blank cells in the bordered range (in this case F1 through J1) IF cell L1 = "NO" and M1 - "YES"


I have written out =AND($L1="NO",$M1="YES") in the formula, and then tried entering isblank(a1:j1) to the "applies to" but this clearly isn't correct.

Any ideas? Thanks in advance

Select the cells A1:J1 then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

=AND(A1="",$L1="NO",$M1="Yes")

Click the Format button | Fill tab and choose your colour, then OK your way out.

Hope this helps.

Pete

Thanks for the fast reply Pete!

That's a good start, but it doesn't quite get the result I need. Using your formula, it highlights the cells only if cell A1 is blank. I need it to highlight only the blank cells (which may change from row to row).

So IF the end cells are correct, then highlight all the blank cells in the row. If you get me.

I really appreciate the quick response though!

Originally Posted by Micksanity

... Using your formula, it highlights the cells only if cell A1 is blank...

No it doesn't - see the attached file.

You should have selected all the cells A1:J1 before applying the CF, then Excel will automatically adjust the cell references to suit those selected cells.

Hope this helps.

Pete

Ohhhhhh sincerest apologies Pete, it actually works perfectly. I got my references mixed up between my example file and my actual workbook.

That's amazing, thanks so much for your help on this!