Conditional Formatting

[Dorian74]

Hi All,

i have the table below. I added the conditional formatinng below t
these cells. however, the "blank" cells are colored red instead of onl
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
+-------------------------------------------------------------------

 

[Spencer101]

Hi All,

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

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

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

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

 

[Claus Busch]

Hi Dorian,

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

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

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

 

[Dorian74]

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

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

Thank you. But it creats a problem. since my table includes th
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 whit
and only then apply the rule where if somethign is greater the "0" the
is colored red.

Thanks

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

 

[Spencer101]

Thank you. But it creats a problem. since my table includes th
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 whit
and only then apply the rule where if somethign is greater the "0" the
is colored red.

Thanks.

I'm guessing by the snapshots you've posted that you're using somethin
later than Excel 2003, so why not use the Table option in the Inser
menu to take care of the alternating blue and white lines rather tha
using conditional formatting

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

 

[Dorian74]

I'm guessing by the snapshots you've posted that you're using somethin
later than Excel 2003, so why not use the Table option in the Inser
menu to take care of the alternating blue and white lines rather tha
using conditional formatting?

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

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

 

[Dorian74]

Then I would suggest you go with Claus' CF formula
*-=AND(ISNUMBER(N4),N4>0)-* ) in combination with the alternating row
one you already have.

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

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

 

[Spencer101]

OK i found a solution with the with using the formula "N4 is between
and 1000"

however, i was woundering regarding pasting the formule in alternat
rows. is there a way to paste it in all the alternate rows automaticall
not manually?

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

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

Hope that helps

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