Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    How is it doing that? (Excel 2000)

    I have a template that was a hand me down. The 'Status' colum has three options [O, C, D] (Arrow, drop down). Dependant upon which letter is selected, the whole row is 'colorized'. I can't find any 'code' that is doing it... and I'm not seeing a macro.

    I'm wanting to do something similar to a different doc.....
    I can't get the file zipped small enough to attach [img]/forums/images/smilies/sad.gif[/img]

    HELP!

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How is it doing that? (Excel 2000)

    It sounds like conditional formatting. With the cells you want to format select Format>Conditional Formatting...

    You can set formats based on a formula or value in a cell.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: How is it doing that? (Excel 2000)

    I was just honing in on that....
    I clicked the row and conditional formatting. This is the "code" for the formula:
    =UPPER(LEFT($C3,1))="R"
    I don't know what "UPPER" stands for
    I don't know what "LEFT stands for...
    I was in Cell C3... Why reference cell C1?
    and for the "R"... When I typed "Retired into the cell, it formatted as expected.

    Do I have to go through EVERY Row to set the "C" column?


    -not quite as confused, Jody

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How is it doing that? (Excel 2000)

    UPPER - converts the value to upper case
    LEFT($C3,1) - takes only the first character of the text in cell C3
    =UPPER(LEFT($C3,1))="R" This ensures that if "r" is entered rather than "R" the formatting still works. As in your example, if your text starts with an R it applies the formatting.

    The conditional formatting formula pointed to one particular cell as it wanted the formatting applied across the whole row when that 1 cell was changed.

    If you select the whole row before you apply your conditional formatting then it will be applied to every cell in the row automatically. If you want it to be based on the value in a particular cell then use absolute reference (eg $C$1)

  5. #5
    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: How is it doing that? (Excel 2000)

    =UPPER(LEFT($C3,1))="R"
    This is:
    Look at the leftmost 1 character in cell C3. Make it uppercase and compare it to the letter R. If that statement is true add the format conditions you have set via the <format> button in conditional formatting.

    The $C3 means that the row number 3 is relative (note it is not "$3") but that the column letter C is "locked"

    When you select a range and set the formula, you base in on the "active cell" in the formula. This formula will always look at column C since it is locked (has the $ no matter what column is selected, but the row will change (no $). You have to highlight the entire range you want conditional formatting at the beginning, you do not need to do it row by row.

    If this is not clear enough, please post back with additional quesitons.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: How is it doing that? (Excel 2000)

    We're getting closer, We're getting closer!!!!

    OK, I highlighted row's 5-16 (the rows that I want to apply conditional formatting to). Into the the 'Formula Is' text box, I entered =UPPER(LEFT($C,1))="R" It doesn't like my 'code'... I don't know what to put after the [b]$c[b] ... I think that's my problem anyway...
    -Jody

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: How is it doing that? (Excel 2000)

    [b]OK.... Work around found.... I still don't know how to "code" it, but if I select paintbrush from the one row that I Contitional Fromatted and paint the rest of the rows....
    IT WORKS <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    PS.... I'd still like to know what range I was supose to have placed in the fromatting box.

  8. #8
    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: How is it doing that? (Excel 2000)

    Since you started in row 5 you should use $C5, the row of the active cell.

    Steve

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

    Re: How is it doing that? (Excel 2000)

    $C is not a valid cell reference. You need to add the row number of the row you are typing the formula into. You said that you "highlighted row's 5-16". If the active cell is in row 5, then the formula should have been:

    <pre>=UPPER(LEFT($C5,1))="R"
    </pre>

    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: How is it doing that? (Excel 2000)

    TY <ALL SMILES>

Posting Permissions

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