Conditional Format to highlight blank cell

  • Thread starter Thread starter DaveMoore
  • Start date Start date
D

DaveMoore

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?
Click to expand...

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) ..
Click to expand...

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 "") ...
Click to expand...

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.
 
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]
 
Back
Top