# Conditional Formatting

#### wiscokid

##### Board Regular
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?

### 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
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
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).

#### Oaktree

##### MrExcel MVP
Barry Katcher said:
My solutions is a work-around, Oaktree's is a direct solution - use his (but pay me).

You can have 30% of my \$0.

Replies
9
Views
176
Replies
1
Views
96
Replies
3
Views
495
Replies
2
Views
112
Replies
7
Views
371