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?

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.

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),N40)



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

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.

I'm guessing by the snapshots you've posted that you're 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?

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

Then I would suggest you go with Claus' CF formula ( =AND(ISNUMBER(N4),N40) ) in combination with the alternating rows one you already have.

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

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

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.

thanks. that would do it