1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Highlight Find/Search Cells

Discussion in 'Business Applications' started by RBG50, Sep 17, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. RBG50

    RBG50 Thread Starter

    Joined:
    Sep 12, 2007
    Messages:
    10
    Yes, can anyone help me figure out how to use the Find and Replace command (Ctrl-F) in excel to highlight the cells that's been found in yellow or any color.

    Sometimes, I would have to verify and match serial numbers from a hardcopy and file that was sent to me. It would be great (and would make life easier) if I could have excel or the Find and Replace command to automatically highlight each cell that is found. Thanks
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Will this be for a table? A sorted list? Do you want to highlight all duplicates? Could you just use Conditional Formatting? Why does the current Find not suit your needs?
     
  3. RBG50

    RBG50 Thread Starter

    Joined:
    Sep 12, 2007
    Messages:
    10
    This is a two column sort list about 1100 serial numbers. I would generally have to cut and paste the serial numbers on the Find and Replace command to match and verify from the other file.

    By highlighting each of the numbers found by F/R command it helps me not to loose track of where I'm at on the list. If you know a better way to do this please help. Thanks
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The entire process can be automated. Post a sample workbook and one of us will whip up the code for you.

    Regards,
    Rollin
     
  5. RBG50

    RBG50 Thread Starter

    Joined:
    Sep 12, 2007
    Messages:
    10
    How do I post a sample workbook on this forum? And I'm I allow to?
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    When you click "Post a Reply" you should be taken to the posting page where you will find a "Manage Attachments" button toward the bottom. I can't quite remember whether or not you need to have a certain number of posts in the forum before you can add attachements. If not, email to me and I'll post for others to see.

    Send to Rollin_Again at hotmail dot com

    Regards,
    Rollin
     
  7. RBG50

    RBG50 Thread Starter

    Joined:
    Sep 12, 2007
    Messages:
    10
    Rollin, I will email you two (2), sample files. Thanks
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I am attaching the files to this post in case anyone else would like to help or provide input.

    Now that we are able to see the files explain to me what you would like the macro to do

    Regards,
    Rollin
     

    Attached Files:

  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You can use a formula in each workbook to determine whether a match is made on the other workbook. In my examples below we will refer to the first workbook as "Workbook1" and the second workbook as "Workbook2" Just change the workbook names in the formula to reflect the true names of your workbooks.

    In the first workbook (Workbook1) select an unused cell in the first row of data and enter the first formula below. In the first example the formula will search for the value of A1 in the Range A1:A300 in the second workbook . After entering the formula in the cell you can copy the formula down for all subsequent rows of data. If the value is not found in the second workbook the cell will display "Not Found." Repeat the same steps in Workbook2 using the second formula to determine if a match is made in Workbook1. After the formula is applied to all rows of data in each workbook you can simply sort the data or autofilter to show only the records that meet your criteria. Hope this helps!

    =IF(COUNTIF([Workbook2.xls]Sheet1!$A$1:$A$300,A1)>0,"","NOT FOUND")

    In Workbook2 you can add the same formula to look for a match in Workbook1 range A1:A292

    =IF(COUNTIF([Workbook1.xls]Sheet1!$A$1:$A$292,A1)>0,"","NOT FOUND")



    Regards,
    Rollin
     
  10. RBG50

    RBG50 Thread Starter

    Joined:
    Sep 12, 2007
    Messages:
    10
    Thanks, I will give it a try...
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/625334

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice