Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Highlight rows (2003)

    I don't know if this can be done but I have a list of numbers that I have to find on a spreadsheet and then highlight those rows that the number is in. The numbers can appear multiple times and all must be highlighted. The numbers that I am finding are sorted by this number so multiple instances would be together. Is there any way of highlighting the numbers I need to find and then have them found on the spreadsheet and their rows highlighted with gray? This used to be an easy task until the numbers I need to find have increased. Thanks for any help you can provide.

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

    Re: Highlight rows (2003)

    This can be done usiing Conditional Formatting. It would be easiest to explain if you could post a small sample workbook (data can be fake).

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight rows (2003)

    Attached is a small portion of the spreadsheet where I have highlighted some of the numbers I need to find and highlight. Right now there is about 50 CL Num that I need to find on this spreadsheet and highlight the rows.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Highlight rows (2003)

    Let's say you have your list of numbers to find in a range named NumList. Select your data list (columns A:E in your example), choose Format-Conditional Formatting, change the dropdown to Formula Is and enter =NOT(ISERROR(MATCH($A1,NumList,0))) and then click on the Format... button, choose the Pattern you want and then press OK twice.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Highlight rows (2003)

    PS Because the numbers you are looking for are numbers stored as text in the table, they either need to be stored as text in your list of numbers to find, or you will need to alter the formula to:
    =NOT(ISERROR(MATCH(VALUE($A1),NumList,0)))
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Highlight rows (2003)

    As an alternative to Rory's suggestion, enter the numbers for the list in a column formatted as text. See attached workbook.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight rows (2003)

    Thanks so much - worked perfectly. This will save a lot of time.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight rows (2003)

    On the same worksheet I am trying to use the Grp Num field to highlight the rows because I also could use the list of Grp Nums to match. The column that the Grp Num field is in is B. I put the Group Numbers (that I need to match) in the same place I put the Cl Nums (the numbers are text) and named that range. I thought it would do the same thing but it is highlighting everything. It seems like it would work no matter what numbers I was matching.

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

    Re: Highlight rows (2003)

    The formula for conditional formatting should now use $B instead of $A, since you're trying to match on column B.

    If that doesn't help, could you post a sample again? I'm not sure I understand what you've done.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight rows (2003)

    Sorry, I am wrong. I was using the wrong set of Group Numbers. It works. Thanks for your help.

Posting Permissions

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