Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (2k)

    I did a quick search and found some posts on the subject but wasn't sure if it was what I needed...

    Basically what i wanna do is conditionally format a cell's back color for an entire row:

    if cell value < cell value in another column, then purple-ize the current cell

    is there a formula to do this?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Conditional Formatting (2k)

    You can probably use conditional formatting, but you'll need to provide a more precise and exact description of what you want to accomplish.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2k)

    K, heres an attached example where i put in the conditional formatting for 4 cells, individually... but thats a tedious process that I would much rather have a "drag-down" formula for, or a macro if thats a better method to do what im trying to do.

    let me know if im still not benig clear enough, thanks for the help
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Conditional Formatting (2k)

    Thanks. It's easy to do it in one fell swoop, as follows:
    - Select the range to which you want to apply conditional formatting (in your example, B1:B4)
    - Select Format | Conditional Formatting...
    - Select Cell Value Is, Less Than, and enter =A1 in the box.
    - If you click on A1, Excel will insert an absolute reference =$A$1; convert it to a relative reference by removing the $ characters, or by pressing F4 three times.
    - Click Format... and specify the formatting you like.
    - Click OK twice.
    The crucial point is the use of a relative reference. This ensures that the conditional formatting is set correctly for the other cells in the selection.

    Alternatively, you can apply conditional formatting to B1, again using the relative reference =A1 in the condition, then use the Format Painter to propagate all formatting, including conditional formatting, to B2:B4.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2k)

    1- Hilite all of the cells which you want to conditionally format like this, making sure that cell B1 is the active cell.

    2- Select Conditional Formatting in the Format menu.

    3- In the drop down list select "Formula Is".

    4- Enter this formula in the formula text box:

    <pre>=AND(B1<>"",B1<$A1)
    </pre>


    5- Click the Format button and set the desired format.

    6- Click OK.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2k)

    For what he is doing, I think he wants to use $A1 in the formula (the first message sounds like the conditional formatting is going to be used across the row as well as down the column). I also think there needs to be a check for an empty cell since an empty cell is less than a positive numeric value and would cause empty cells to be formatted.
    Legare Coleman

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

    Re: Conditional Formatting (2k)

    It's not my day, is it? I think I'l quietly withdraw from the Excel forum for now...
    <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2k)

    Well, it was a little confusing. The original post said he wanted to check greater than and that he wanted to do it accross a row. The example check less than and did it down a column. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2k)

    sorry, that was my fault! i meant that the values that were being compared lay in the same row, and then like Legare pointed out, down a column. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    thanks for the help guys!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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