Conditional Formatting

wiscokid

Board Regular
Joined
Aug 2, 2004
Messages
112
I am trying to set a cell equal to blank and keep my conditional formatting statements from being triggered (cell color left white)

The issue I am having is for the following formula:

=IF(D5<0.02,,D5)
this sets the cell = 0.0 if d5<.02
(I want the cell to be blank, not contain 0.0)

If I use
=IF(D5<0.02," ",D5)
then it makes the cell blank (which I want), but my
conditional formatting makes the cell green
my conditional formatting is:
If cell >.10 then green
If cell between .05 and .10 then blue
if cell between .02 and .05 then yellow

If the cell is less than .02, I would like the cell to be blank and not be formatted (kept white)

any ideas?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Use =IF(D5<0.02,"",D5) to make your blanks.

Then, in the conditional format condition declaration, instead of using cell value is, try formula is =AND(ISNUMBER(A1),A1>0.1) where A1 is the activecell to which you're applying the format. The ISNUMBER function should take care of your blanks.

(Similarly, try formula is =AND(ISNUMBER(A1),A1>0.05,A1<=.10) for the blue condition.)
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Yes, I see what you mean; don't know why it's doing that. To fix, make your first conditional statement If cell is between .10 and 1000 or some other outlandishly high number that will never be reached.

EDIT: My solutions is a work-around, Oaktree's is a direct solution - use his (but pay me). :biggrin:
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Barry Katcher said:
My solutions is a work-around, Oaktree's is a direct solution - use his (but pay me). :biggrin:

You can have 30% of my $0. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,144
Members
412,306
Latest member
fabio6
Top