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

"Bob I" wrote:

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 wrote:
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" wrote:


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 wrote:


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" wrote:



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

jmj713 wrote:



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" wrote:




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 wrote:




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

"Bob I" wrote:





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 wrote:





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

"David Biddulph" wrote:






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

"jmj713" wrote in message
...





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" wrote:






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

"jmj713" wrote in message
...





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?