# Solved: Conditional Formatting - Blank Cells are red when empty



## Angusn1 (Feb 9, 2011)

I'm having issues with the following. It seems simple to me but can't seem to get things going right.

What I'm looking to have happen is in Cells C6 through C32 (all of which have a formula - which is what is causing me the grief I think) is when the cell is less then 0 colour green, when the cell is greater then 0 colour red and when the cell is 0 or empty do not colour. 

What I have makes the greater then % go red, the less than go green and the 0 stay white. it's when the cell is blank that my problem happens. I also want this cell white when there's no visible number.

If I do just the two formatting of greater and less then the blank cells stay red when I want them no colour. So that's what I'm stuck on. Any Ideas? I've trolled around other sites and tried different things including posting to another site with no immediate luck. This site might be a little better as I can upload the file.

Thanks in advance.


----------



## officework13 (Mar 2, 2004)

you shoud be able to do this by using 2 conditions.
highlight the cells you want this to apply to
if using excel 2007 this is how to do it. i am not certain on earlier versions.
select format only cells that contain, within conditional formatting.
then use the less 0 and set formating to green
then create a second condition but this time >0 is red.

I tried this and even with a formula in the cell (add a1 +b1)
negatives are green
positives are red
zeros are while
delete the formula and the cell stays white.

if this does not help or if i got it mixed up, you may also be looking for using the 'isblank' function which can be put into the 'use a formul to determine which cells to format. this will apply formatting to every cell until something is entered.

another option is to use the and feature in which if all conditions are met then it will apply formatting.

hope this helps.


----------



## Angusn1 (Feb 9, 2011)

For the most part it is working correctly and I can see it will work with just two conditions is just that formula in column C cells that stay blank because there is no weight in column B cell are staying red when I would like them to stay white.

When you look at the example the negative stays green, the positive stays red, the 0 value (based on equation) stays white. All of this is good so far but the remaining cells in the first column stay red. What I'm trying to have happen is keep the formula in there but have the cells stay white until the weight is put in which causes the math to happen then have the cell in column C to be formatted depending on the answer.


----------



## officework13 (Mar 2, 2004)

i will not be able to help you until tomorrow as i will have to look at the attahced file in detail.


----------



## Angusn1 (Feb 9, 2011)

That's ok, can't put a time limit on help. I'm heading out as well for a valentines dinner so I'll be busy as well. Thanks again. The wost part about it is I know its going to be simple. LOL


----------



## officework13 (Mar 2, 2004)

This will both Educate and assist you.
the funny thing, is that 2 days ago, i ran into a similar problem and came across a few sites that gave some good explanations on conditional formatting, so i thought i could repay the favor.
just reading your post here, without looking at the cells and conditional formatting you had applied as well as the formulas in column 'c', I would never have figured it out. however, after looking at your formulas I see your problem. Now here is your formula in this cell.
=IF(B31="","",((B31-B30)/B30)).
This puts text into the cell and so it does not think that it is a zero or blank

couple ways to handle this, but first i recommend cleaning up all the rules you have on this page, all set for column C. you have duplicates b/c you did a rule for a column and then you did some more for specific cells. this can cause a bit of grief when later making changes to rules.
so b/c you want this to apply to cells c6 to c32 you can go into the manage rules and select 'show formatting rules for' and select this worksheet instead of this selection. (or highlight the entire 'C' column and it will show the rules for the selection.
Now you will see that you have 11 rules which could be simplified down to using 3 rules, all of which you already have they just need tweaking.

second thing is that rules are applied in the order they come in the list and if the first rule is true and then the other rules will not be applied if they are for the same type of formatting (i.e. that is you cannot tell it to format the cell red and then tell it to format it white if the condition is true for both (only 1 colour can be applied and it will be the first rule met. However, if the first rule is bold and then second is colour, then both rules will be applied). 
so given that putting in the "" text results in it looking like a number greater than zero, and since your 'red' rule was before your 'white' rule, the red rule was the first and only rule you that was applied when the cell was blank ("").

Third thing is that your white rule is not actually a colour but rather the cell is not formatted. Therefore, you have two choices, but in either case the white rule has to come before the red rule. If you use the 'no formatting colour' when the cell is blank, then you must use the 'stop if true' otherwise it will apply the red colour. However, you can also use the format colour 'white' which looks the same as not formatting, and since the first rule is to apply white colour, then the red colour cannot be applied. In this case you do not need to use the 'stop if true'.

So here are the 3 rules that you need and in this order (note the green can be before the white).

Cell value <0, format green, applies to =$C$6:$C$32
Formula: =B6="", format white, applies to =$C$6:$C$32 OR
Formula: =B6="", format none, applies to =$C$6:$C$32 (must check stop if true)
Cell value >0, format red, applies to =$C$6:$C$32

That should work
Couple other things.
Instead of using the following formula based rule depending on cell B6,
Formula: =B6="", format none, applies to =$C$6:$C$32 (must check stop if true)
You could also use the following
Cell value ="", format white (or none with stop selected), applies to =$C$6:$C$32 
However, if using this rule, for some reason when you click apply, you have to go back in and edit the rule and delete several of the quotations as excel automatically puts some extra ones around them.

All the best.


----------



## officework13 (Mar 2, 2004)

OH, by the way, if you are as fortunate as me, you will find that this site is one of the better ones in assisting you solve your problems.


----------



## Angusn1 (Feb 9, 2011)

Sorry for the delayed reply....life was a bit busy. 

I have to thank you very much. What you suggested worked like a charm but I have to admit I haven't read your explanation yet. I do have it printed out and will read it, but it'll be more likely on the weekend.

Hopefully I can figure out more then I ask for help....you know how well a guy and asking for help works....LOL


----------

