Cond Formatting driving me MAD!

[ad1234]

I want values >= 1 Green, = 0 Amber, <= -1 Red.... and blanks to remain unformatted. It is the blanks bit that is causing me difficulty/ I have a formula in the cells, removing this by doing copy/paste special/paste values doesn't work.

I've tried all I can think off, looked online etc. but cannot find how to do this simple (in older versions) task. I know this must be very simple!! Help please!?

Thanks

ad1234

 

[Weaver]

What happens if you set all the cells to amber and make the cell ="" the condition to test for instead?

Also, I think the order you apply the conditions is important as I believe once a condition is met, the formatter doesn't look at any of the others.

 

[ad1234]

I'm getting a bit further - thanks!

Three rules...

Cell Value < 0 then red
Cell Value = 0 then amber
Formula: = "" then clear format

These all work - as soon as I put in a fourth rule:

Cell Value > 0 then green all the cells which are blank or more than one turn to green.

I've tried re-ordering rules, pre-filling cells - all permutations but I still cannot stop the empty cell going green... even if I copy/paste special and choose value only......

Also if I remove all formatting and then apply the Cell Value > 0 then green, all blanks and all with > 0 go green..... I think this is obviously something that is not directly to do with the formatting but a hidden value in the cell? I'm frustrated!

 

[jim may]

What Excel Version are you using?

 

[Kentman]

The > 0 needs to be before the ="" but you don't need the '=""' as this is your default condition so will be blank if none of the other conditions are met.

 

[ad1234]

2010.

I've done it now using three "Between" values - between 1 and 100 then green, between 0 and 0 then amber, between -1 and -100 then red and it now does what I want but it is a very messy and unsatisfactory way of doing this!

I've been doing CF for years and years and 2010 has thrown all I know out of the window.... Just checked with some other colleagues and they seem to have experienced the same frustrations at some point since 2010 was rolled out here.

 

[ad1234]

The > 0 needs to be before the ="" but you don't need the '=""' as this is your default condition so will be blank if none of the other conditions are met.

I have tried that but I'm still getting all green.....

 

[jim may]

Conditional Formatting prior to 2007 was solid..

Since 2007 and 2010 (the near same platform) conditional Formatting has (in my opinion) gotten "all screwed up". Traditionally, (since Excel 97) when engagng C/F
on a worksheet - I'm use to selecting oly ONE cell to setup the formatting on -- and 95% of the time (if not 99%) I've always selected the "FormulaIs" option, enter a Formula and "BAM", it works!! -- From there I'd use the Format Painter to apply the One to the Many other cells... TRY DOING THIS IN 2007 or 2010 - It seems to work "on the surface" - But when you go back into MANAGE RULES ---- Holy Crap!!! -- I've seen 10, 20 or more rows of Items -- C/F in 2007 and 2010 is SHAKEY, to me..

 

[DonkeyOte]

A Null String ("") is a 0 length text string, text > number hence Null String > 0

You can use a formula based test:

=SUM(A1)>0
format Green

 

[RoryA]

I'm getting a bit further - thanks!

Three rules...

Cell Value < 0 then red
Cell Value = 0 then amber
Formula: = "" then clear format

These all work - as soon as I put in a fourth rule:

Cell Value > 0 then green all the cells which are blank or more than one turn to green.

Did you check the Stop if True option for rule 3?