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?
 

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
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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
8,053
Office Version
  1. 365
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)
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top