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,"",VLOO KUP(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" wrote in message
oups.com...
I have this formula entered into a range of cells -
=IF(ISNA(VLOOKUP(B5,April,D$2,FALSE))=TRUE,"",VLOO KUP(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(B 5,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 <atyahoo<dotcom
----
"DaveMoore" wrote in message
oups.com...
I have this formula entered into a range of cells -
=IF(ISNA(VLOOKUP(B5,April,D$2,FALSE))=TRUE,"",VLOO KUP(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]


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=381508