Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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)

    Thanks in advance

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    - Ricky

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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.

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

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (XL2003 SP2)

    Try this...
    Attached Images Attached Images
    - Ricky

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (XL2003 SP2)

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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (XL2003 SP2)

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

    <hr>
    My post should have read
    If C127 = 0 AND IF C119:C124 >0
    <hr>

    as an amendment to the original post. As it is unclear I have assumed that any of the cells in C119:C124 should be greater than 0. If it is the total of those 6 cells then I agree with you and Ricky
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •