Conditional Format to highlight blank cell

I have this formula entered into a range of cells -
=IF(ISNA(VLOOKUP(B5,April,D$2,FALSE))=TRUE,"",VLOOKUP(B5,April,D$2,FALSE))
This will produce either a number (negative or positive or zero) or a
blank cell. (Is "" a blank cell?)

I have conditionally formatted the range to change the font colour to
white if the value is equal to zero (effectively hiding zero's) and
wish to add an additional condition changing the cell colour to grey if
it is a blank cell (or equal to "").

How do I / Can I do this?

The formula above works, but can it be improved?

My grateful thanks to all who respond.
Dave Moore

You can simply add one more conditional format to treat the blank ""

Mangesh



"DaveMoore" <[email protected]> wrote in message
news:[email protected]...
> I have this formula entered into a range of cells -
> =IF(ISNA(VLOOKUP(B5,April,D$2,FALSE))=TRUE,"",VLOOKUP(B5,April,D$2,FALSE))
> This will produce either a number (negative or positive or zero) or a
> blank cell. (Is "" a blank cell?)
>
> I have conditionally formatted the range to change the font colour to
> white if the value is equal to zero (effectively hiding zero's) and
> wish to add an additional condition changing the cell colour to grey if
> it is a blank cell (or equal to "").
>
> How do I / Can I do this?
>
> The formula above works, but can it be improved?
>
> My grateful thanks to all who respond.
> Dave Moore
>

Thanks Mangesh.
In fact I had tried this (entering "" ) and excel changed the condition
to read =""""
After your post I retried entering ="" and now it works.
Thanks again
Dave Moore

Just some thoughts ..

> The formula above works, but can it be improved?

The "=TRUE" part is not necessary, so the formula could be just:
=IF(ISNA(VLOOKUP(B5,April,D$3,FALSE)),"",VLOOKUP(B5,April,D$3,FALSE))

> I have conditionally formatted the range to change the font colour to
> white if the value is equal to zero (effectively hiding zero's) ..

As a simpler? alternative, to hide/suppress all zeros display in the sheet,
we could just make the setting:
Tools > Options > View tab > Uncheck "Zero values" > OK

> ... condition changing the cell colour to grey if
> it is a blank cell (or equal to "") ...

Assuming the range of cells is C5:C10 (formula above copied down from C5)
just select the range (C5 will be active), and then conditionally format
with the formula: =C5="" in the desired gray fill. The CF formulas for each
cell in the range will auto-adjust relatively to suit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"DaveMoore" <[email protected]> wrote in message
news:[email protected]...
> I have this formula entered into a range of cells -
> =IF(ISNA(VLOOKUP(B5,April,D$2,FALSE))=TRUE,"",VLOOKUP(B5,April,D$2,FALSE))
> This will produce either a number (negative or positive or zero) or a
> blank cell. (Is "" a blank cell?)
>
> I have conditionally formatted the range to change the font colour to
> white if the value is equal to zero (effectively hiding zero's) and
> wish to add an additional condition changing the cell colour to grey if
> it is a blank cell (or equal to "").
>
> How do I / Can I do this?
>
> The formula above works, but can it be improved?
>
> My grateful thanks to all who respond.
> Dave Moore
>

Regarding your sub-query, (Is "" a blank cell?), the answer is NO!
The empty strings denote that the cell is NULL but not empty and not Blank.

To test this, put in cell A2 the formula =If(A1=999,A1=A2,""). Make sure cell A1 is blank. The above formula will certainly return " " (empty strings) in Cell A2. Next, put in Cell B2 =ISBLANK(A2) to test for emptiness or blankness in A2. This 2nd formula will return FALSE although Cell A2 will appear BLANK to the eye. The lesson is that the presence of " " render a cell NON-EMPTY or NON-Blank, if you will.
[PS: you can also, in code, use IsEmpty(Range("b2")) to test]