Excel - Conditional Formatting

Hi All,

i have the table below. I added the conditional formatinng below to
these cells. however, the "blank" cells are colored red instead of only
cells with value greater then "0".
anyone?


+-------------------------------------------------------------------+
|Filename: table1.JPG                                               |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=319|
|Filename: Rule.JPG                                                 |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=320|
+-------------------------------------------------------------------+



--
Dorian74

Hi Dorian,

Am Thu, 5 Apr 2012 06:21:38 +0000 schrieb Dorian74:


a cell has value=0 if the cell is empty. But you have a formula in it.
Try CF with formula:
=AND(ISNUMBER(N4),N4>0)



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Dorian74;1600484 Wrote:

You need to add another condition.  This one based on a formula and
before the one you already have.  See the attached pic.

Make sure you set the background colour in the first condition as "No
Fill" and it should work just fine.


+-------------------------------------------------------------------+
|Filename: ConditionalFormattingRules.JPG                           |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=321|
+-------------------------------------------------------------------+



--
Spencer101

Spencer101;1600490 Wrote:

Thank you. But it creats a problem. since my table includes the
follwoing condition conditioning: =MOD(ROW(),2)=0
i end up with the picture below.
How can i keep the format where i have one line blue and the other white
and only then apply the rule where if somethign is greater the "0" then
is colored red.

Thanks.


+-------------------------------------------------------------------+
|Filename: Capture2.JPG                                             |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=322|
+-------------------------------------------------------------------+



--
Dorian74

Dorian74;1600491 Wrote:

I am guessing by the snapshots you have posted that you are using something
later than Excel 2003, so why not use the Table option in the Insert
menu to take care of the alternating blue and white lines rather than
using conditional formatting?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Spencer101

Spencer101;1600502 Wrote:

Hi, i'm just not that creazy about the way it screws up the table i
already made.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Dorian74

Spencer101;1600512 Wrote:

is there a way to paste it in all the alternate rows automatically not
manually?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Dorian74

Dorian74;1600595 Wrote:

If you write the formula in the first row then highlight that cell and
the one below, you can just drag the two down to copy.  That will put
the formula in all the even numbers (for example) and leave the odd ones
blank.

It will also work if you have two formulas that you want to alternate
all the way down.  Put formula one in the first row and formula two in
the second, highlight the two cells and drag down as necessary.

Hope that helps.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Spencer101