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?

Use a formula of

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

--
__________________________________
HTH

Bob

"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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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

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?

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

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

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?

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

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


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


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?

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.

"Bob I" wrote:

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

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


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


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?

All I can suggest is to review the cell value contents and the rule set
points.

jmj713 wrote:

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.

"Bob I" wrote:


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


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



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



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?