Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a blank cell with a date format blank? | Excel Worksheet Functions | |||
Custom format that shows blank cell if another cell is empty | Excel Discussion (Misc queries) | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |