Conditional Formatting Help

[jmj713]

I need to use Conditional Formatting to shade a few rows on data based on
their value. Problem is there are cells with zeros and some are empty. I need
to skip these cells. How can I do that?

 

[Bob Phillips]

Use a formula of

=AND(A1<>"",A1<>0)

 

[David Biddulph]

Use AND or OR functions as appropriate in your CF Formula Is condition.
What criterion are you currently applying?

 

[jmj713]

I currently have a simple Highlight rule, "Format all cells based on their
value", with the lowest colored red and the highest colored green. Where do I
need to include the formula to skip zero and empty cells? When I change the
Type to "Formula" and enter the formula provided in the preceding post, Excel
gives me an error, saying "You cannot use relative references..."

 

[David Biddulph]

Are you using Excel 2007? If so, I can't help you.

 

[jmj713]

I am. Hopefully someone else with Excel 2007 can. Thanks.

 

[Bob I]

Format all cell based on their value. Then "select" Minimum, and Number
and put in what the minimum value should be like .01 or whatever.

 

[jmj713]

No, unfortunately the cells still get colored red, and I need them to be
ignored.

 

[Bob I]

Here is a possible work around. Use a 3 color scale. Make the "Midpoint"
Red, and the "Minimum" White. Put the number 0 in Minimum and 0.1 in
Midpoint.

 

[jmj713]

That works to a degree, but sadly it doesn't fix the problem. What this
workaround does is just color the zero-cells white instead of red, but it
keeps their value in mind when coloring the other cells green. So I don't get
the lowest cells shaded red at all, only shades of green. I'm astonished
there isn't a way to specifically ignore cells with zeroes in Conditional
Formatting.

 

[Bob I]

Then you didn't make the Midpoint Red as instructed.

 

[jmj713]

I double-checked your directions. I did as you'd said. Here's a screenshot:

http://img7.imageshack.us/img7/8378/42987526.png

 

[Bob I]

I stand corrected there. Sorry. The issue is related to the numbers
used. In this case you have nothing remotely near 0.1 so pick the lowest
number that you will have (not your 0 or Blank) and put that in the
Midpoint. Let us know how you come out.

 

[jmj713]

I get it now. The problem then is this becomes rather cumbersome to
implement, since this should be automatic. It kind of defeats the purpose if
you need to speficy the lowest number each time (in this case my lowest
number is 38). I think I'll just have to keep doing it as I had done in the
past, just manually selecting only the cells with data and conditionally
formatting them each time. I was just looking for a way to have this done
automatically, but it appears there's just no way.

 

[Bob I]

there is another method, but requires multiple rules. You need to check
for blank and set no formatting. Stop rule. Check for 0, set no
formatting, stop rule. And then set your two color rule.

 

[jmj713]

Okay, I think I know what you mean, but could you be a bit more specific?
Thanks!

 

[Bob I]

New Rule "format only cells that contain. equals 0 tick the box for stop
if true
then Manage rules New Rule "format only cells that contain. equals ""
tick the box for stop if true
then Manage rules New Rule format all cells based on their values.

then make sure the "color one" is at bottom or last on the list.

 

[jmj713]

This is so frustrating... I did just as you described and the result is the
same: the zero cells are white, the cells with other numbers are all shades
of green. No red.

 

[Bob I]

Pure Red is applied to the very lowest number, pure green to the very
highest and the color change/distribution is constant from high to low.
Perhaps you have some null in the area/range or a number that is set
really close to Zero. That number or null would be pure red and then you
have a "number gap" going up to what you believe is your first number.

 

[jmj713]

The numbers I need to format are received via a COUNTIF formula, and if there
are no values, it returns a zero, which is hidden. So I'm not sure why this
is happening. There are only either these zero cells, or the various numbers.