# 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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,995
Messages
5,834,779
Members
430,321
Latest member
yemisimi11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back