Conditional Formatting - Greater then / Less then / 0 or blank

angusn

New Member
Joined
Jan 20, 2011
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I'm trying to do something that I think is simple but just can't get the last conditional format.

I have 2 columns B (weight) & C (% up or down) with 32 Rows. It is a weight loss chart that colour codes the ups and downs.
Cells in Column C have the formula "=IF(B32="","",((B32-B31)/B31))" to leave the cell blank if the cell in column B has no typed in value.


The formatting I have is as follows:

1. If Cell C32 is Greater than 0 shade it Red - Works
(Cell Value Is less than 0)

2. If Cell C32 is Less than 0 shade it Green - Works
(Cell Value Is Greater than 0)

3. If Cell B32 is Empty than C32 should have no colour. - Problem is that Cells Stay Red.
Formula Is =IF(ISBLANK(B32),"",IF(B32=0,B32,""))

Any quick ideas?

....but now that I read it a couple times I would need something that also states if cell in column C is equal to zero do not shade as well.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try:
Sum(B32) = 0

Sum() will ignore the "" values and alsos capture blanks as zeros. Should work for you I think... (though I'm not clear whether you are trying to conditionally format cells in column B or Column C, or both)
 
Upvote 0
The conditional formatting is only in Column C.

That didn't seem to help any.

Is it possible upload the excel sheet at all?
 
Upvote 0
=IF(ISBLANK(B32),"",IF(B32=0,B32,""))

This is a long formula just to tell if a cell is empty. It doesn't really make sense. Can you try again to describe clearly what you have and what you want to achieve? Formulas used in conditional formatting must evaluate to TRUE or FALSE, but your formula can return and empty string (""). It is as if you are describing the formulas in the cells rather than your conditional formatting.

Also of course the original formatting in the cell (when no conditional formatting applies) must not be a color such as red.
 
Upvote 0
That formula ( =IF(ISBLANK(B32),"",IF(B32=0,B32,""))) is something I found online ( http://www.excelbanter.com/showthread.php?t=184071) and seemed to work only halfway though. But I am open to suggestions for sure.

What I'm trying to have happen is this:

Column B I type in my weight and in column C (formula spits out a percentage based on previous weight entry) I would like the cell to be red if it is greater than 0 or green if less than 0 or stay white if there is no number.

But I think because I have a formula in column c, it is treating it as a greater then value because all the empty cells are shaded red.

Just found the insert image button...created a online picasa album to upload a screenshot. Might help to see it. If that doesn't work go here: https://picasaweb.google.com/100554812738917197351/OddsNEnds#

OddsNEnds
 
Upvote 0
You need to read up on conditional formatting so you understand it better. There is no need for a formula, just use:

Conditional format #1: Cell Value Greater Than 0, Format Red
Conditional format #2: Cell Value Less Than 0, Format Green

There is no need to take action if the cell is blank - that will be the default (normal or unchanged) format that is in the cell if neither conditional format applies.

ξ
 
Upvote 0
All I can say is keep trying and you'll get it eventually. It's the sort of thing that takes two seconds to show and 2 hours to explain. If you really want help you'll have to report what version of Excel you have and exactly (in excruciating detail) what you are doing to create the conditional format: what buttons you hit, what options you select, what values you enter, and so on.

ξ
 
Upvote 0
I would also suggest you "practice" on a simple worksheet with only one cell - try to get it to show up green when you put a 1 in it Then add a second CF, to have it go red when you put a -1 in it. Then you're done. If you have it working in one cell you can "copy" the format to the rest.

Here's one walk through (for Excel 2007). You can find You Tube videos if you search google! It's a common topic, really. Just start searching Excel 2007 conditional formatting (or Excel 2003 if that's what you are using):
http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html
 
Last edited:
Upvote 0
Thanks for trying and the shove in a good direction. I'll have a look at the sites suggested and see if I can get something to work. Like it shows in the first example over is red, under is green, zero is white...it's just that the C column cells that holds a formula in rows 13 to 32 keep turning red on me.

If I find something that works I'll post it back here so that way I might not be speaking so much Greek.

Thanks again for trying, and here's to learning. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top