Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Conditional Formatting

  1. #1
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using a spreadsheet with 15 columns (seven with numerical data, the others are company names, etc.) and 2500+ rows.

    I have set up 11 buttons at the top of the spreadsheet for each of the regions (one of the columns). Also, I have included array formula to sum up the totals for the seven numerical columns. So each regional total for each of the seven categories can be seen.

    I also have set up conditional formatting, so that when one of the buttons is selected, the corresponding region items will be highlighted in the table. However, I can get only the regional name to highlight, not the entire row.

    Is there any way to highlight the entire row istead of just the one column with conditional formatting?

    (I'm using Excel 97 - soon to be 2000.)
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, have the entire row selected when you apply the Conditional Format. Also, use a Formula Is condition.

    [ This Message was edited by: Mark W. on 2002-03-22 13:46 ]

  3. #3
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried that. In fact, I had selected the entire table to do that. Then only portions of the table. But still only the one column is highlighted. I must be missing something.
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Did you use a Formula Is condition? I added that to my 1st reply while you were posting. The cell reference used in the condition must be mixed reference (e.g., $A2).

    [ This Message was edited by: Mark W. on 2002-03-22 13:49 ]

    [ This Message was edited by: Mark W. on 2002-03-22 13:49 ]

  5. #5
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe a further explanation. Because there are 11 regions, Excel will not allow more than 7 Nested IF statements in one formula. So, I broke the buttons into six regions for one cell reference (C3)- which then dispalys the region name. Then the other five regions I have linked to another cell reference (C5)- which displays the names. Now, the formulas for the regional summaries are in two different rows (3 and 5).

    Anyway, now to the question about the Formula Is, in conditional formatting, do I have to reference both cells C3 C5)? Or are you referrring to something else.

    Keep in mind I'm self taught on all this, so I may ask dumb questions. But thanks for hanging in there with me.
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For example...

    A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4.

    [ This Message was edited by: Mark W. on 2002-03-22 14:06 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What's the condition that your IFs are testing?

  8. #8
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was using cell references in the Cell Is. Thus, if C3 = Region 1, then yellow pattern. But because I have another cell reference involved, namely C5, I can't see anyway to make it work.

    Here is the formula in C3:

    =IF($B$1=1,C8,IF($B$1=2,C9,IF($B$1=3,C10,IF($B$1=4,C11,IF($V$1=5,C12,C13)))))

    (I'm using $B$1 as the reference, and C8-C19 is the shortened list of regions)

    Now, if you are talking about Formula Is, I'm not sure what reference goes there.

    _________________
    - old, slow, and confused
    ... but at least I'm inconsistent -

    [ This Message was edited by: shades on 2002-03-22 14:08 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 13:57, Mark W. wrote:
    For example...

    A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4.

    [ This Message was edited
    by: Mark W. on 2002-03-22 14:06 ]
    Consider this extension to my previous example. Enter 4 into cell D1. Select A2:B5 with cell A2 as the active cell. Change the "Formula Is" condition to =$A2=$D$1. Now you can change the value in D1 (between 1 and 4) and see a different row highlighted.

    Do you see how this works? Your buttons should change the content of a cell or the value of a defined name and the conditional format should test for that value.

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 14:12, Mark W. wrote:
    On 2002-03-22 13:57, Mark W. wrote:
    For example...

    A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4.

    [ This Message was edited
    by: Mark W. on 2002-03-22 14:06 ]
    Consider this extension to my previous example. Enter 4 into cell D1. Select A2:B5 with cell A2 as the active cell. Change the "Formula Is" condition to =$A2=$D$1. Now you can change the value in D1 (between 1 and 4) and see a different row highlighted.

    Do you see how this works? Your buttons should change the content of a cell or the value of a defined name and the conditional format should test for that value.
    Why not use the formula, =INDEX(C8:C19,B1), and reference the results of this formula like I did for D1 in the example above?

    [ This Message was edited by: Mark W. on 2002-03-22 14:16 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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