Conditional Fomatting, VLOOKUP and Blank Cell in a range?

[JeffK627]

I need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried =VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment). Can anyone help me with this? I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.

 

[T. Valko]

I need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried =VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment). Can anyone help me with this? I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.

Try this...

=OR($D$2:$D$21="")

If you test that in a cell on the worksheet then it must be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

When entering the formula in the conditional formatting formula box (the refedit), it will automatically be processed as an array and no special entry is needed.

 

[JeffK627]

That worked, thanks!

 

[T. Valko]

That worked, thanks!

You're welcome. Thanks for the feedback!