Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2008
    Location
    Irvine, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (2000/SP-3)

    I've been trying to logic this out but I'm having trouble. The conditions are:
    If any cell in a row is negative and the cell above that cell is positive, that row should be shaded *but only up to that cell, not beyond it*
    If it helps, the cell to the right will always be positive.

    My spreadsheet has ~30 columns and could have any number of rows. The formulas return values for each column that gradually decrease to well past zero, and that spot is further down for each column. Basically, every negative number in a row will be highlighted, but only if the last negative number cell has a positive number in the cell above it.

    Kindof like this:
    + + + + +
    - + + + + <highlight negative #s
    - + + + +
    - - + + + <highlight negative #s
    - - + + +
    - - - + + <highlight negative #s
    - - - + +
    - - - - + <highlight negative #s
    - - - - +

    I hope this makes sense!

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

    Re: Conditional Formatting (2000/SP-3)

    Welcome to Woody's Lounge!

    Let's say that your data begin in cell A1, and extend to column AD (the 30th column).
    Select the data range except its first row, i.e. the range from A2 to the cell in the last used row in column AD.
    Cell A2 should be the active cell within the selection.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the following formula in the box next to it:
    <code>
    =AND(A2<0,INDEX($A1:$AD1,1,MAX(($A2:$AD2<0)*COLUMN ($A2:$AD2)))>0)
    </code>
    Click Format...
    Specify the formatting that you want.
    OK your way out.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (2000/SP-3)

    Select your range (in your example A2: E10 (I presumed row 1 had headers of some sort)

    Format - conditional formatting...
    Formula:
    =COUNTIF($A2:$E2,"<0")>COUNTIF($A1:$E1,"<0")
    [Format] Set format as desired
    [ok][ok]

    Steve

  4. #4
    New Lounger
    Join Date
    Oct 2008
    Location
    Irvine, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2000/SP-3)

    Well, both formulas worked, but the second one seems more reliable for my purposes (plus trying to figure it out won't make my head explode). <img src=/S/blowup.gif border=0 alt=blowup width=60 height=60>
    I know the basics of COUNTIF and AND functions, but all that INDEX and MAX stuff has me baffled!

    Edit: I should clarify, I can use the second one, which will highlight the entire row, and have another condition that overrides the formatting for all the positive numbers, since none of them are shaded.

    Thank you both!

Posting Permissions

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