# Thread: Conditional Formatting (XL2003 SP2)

1. ## Conditional Formatting (XL2003 SP2)

I'd like to create a conditional format for a cell, but can't get a formula to work right.
Basically, I'm hoping to set a condition to turn a cell (C127) red to alert the inputting person that a value greater than zero is expected based on the sum (always positive) of a range of other cells.
I was hoping this might work (at least I'm not getting an error message).
=AND(C127<>0,SUM(\$C\$119:\$C\$124)>0)

2. ## Re: Conditional Formatting (XL2003 SP2)

Am I understanding you correctly? Do you want the cell, C127, to shade red if the sum of C119:C124 is zero or a negative number? Should cell C127 contain any text or perhaps the sum of the range, i.e "=sum(C119:C124)"?
<img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

3. ## Re: Conditional Formatting (XL2003 SP2)

My bad. Perhaps thats what's causing my confusion. Please read my written explanation and ignore what I created in the formula; it should make more sense.

If C127 = 0 AND IF C119:C124 >0 then turn the cell red (where C127 is the current cell).

Try this...

5. ## Re: Conditional Formatting (XL2003 SP2)

Hi There

I always finde it easier to use named ranges try this for the Conditional Formatting of C127:

=AND(C127=0,COUNTIF(myRange,">0"))

I named range C119:C124 as myRange

Enjoy

6. ## Re: Conditional Formatting (XL2003 SP2)

Shouldn't you have SUM(myRange)>0 instead of COUNTIF(myRange,">0") ?

7. ## Re: Conditional Formatting (XL2003 SP2)

Yes possibly but in <post:=650,672>post 650,672</post:> Arcturus16a states

<hr>