+ Reply to Thread
Results 1 to 14 of 14

Extract Highlighted in red

  1. #1
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Extract Highlighted in red

    Hi,

    Can anyone create a VBA function or macro to extract all numbers highlighted in red in my worksheet to sheet 2. In sheet one, there are a few hundred numbers highlighted in red among many not highlighted numbers. I don't want to copy/paste one by one because it may cause errors. I want the computer to extract all number highlighted in red to sheet 2 and leave ten spaces below it. Ex.
    123 456 901 756 146
    486 146 156 183

    Like this, I you can't, just help to make a macro to extract all numbers highlighted in red to sheet 2.

    Please help. Thank you.
    Last edited by Jerry HKA; 10-04-2012 at 10:52 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Extract Highlighted in red

    Hello Jerry HKA,

    Since there is no color in your post, is the cell background color red or is the cell font color red?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    The cell background is colour red. Here is a small smaple of the worksheet. It's only one tenth of my worksheet, but you could use it.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-14-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Extract Highlighted in red

    Hi,

    Create a new module and paste the following codes:

    Please Login or Register  to view this content.
    Then select the entire range you want to search.
    Then excute the above codes.

    Hope this helps.
    Last edited by JBeaucaire; 10-05-2012 at 01:30 AM. Reason: Added code tags, as per forum rules. Don't forget!

  5. #5
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    Any idea how to make it look like the table above at post 1? That is in sheet 2. Can you make the macro paste the nmbers at sheet 2 with the same arrangements in post 1's table? Please.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Extract Highlighted in red

    Hello Jerry HKA,

    I have added the macro below to the attached workbook. There is button on "Sheet1" to run the macro. Let me know if the output is what you want or not.
    Please Login or Register  to view this content.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Extract Highlighted in red

    Hello Jerry HKA,

    The site is FUBAR again. The file upload feature is not working. I will try later to attach the file.


    EDIT: Finally, the file loader is working!! Workbook attached
    Attached Files Attached Files
    Last edited by Leith Ross; 10-05-2012 at 02:03 AM.

  8. #8
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    Please do, I need it very much. I can't seem to work the macro. I will need the file you were going to attach. Please do try again. Thank you.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Extract Highlighted in red

    Hello Jerry HKA,

    See my last post. The workbook is attached to it.

  10. #10
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    Hi Leith, thanks for the macro, but there's a little bit of an error. The number like 039, 017 and numbers that start with 0, came out without the zero. The zero is very important, could you fix it for me, please? Thank you.

    Also, it won't detect 111, or 1, or 11, it won't detect a triple same number.
    Last edited by Jerry HKA; 10-05-2012 at 02:35 AM.

  11. #11
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    By the way, how did you make it work? Once I added a few number to the worksheet and coloured it red, nothing came out in sheet 2.

  12. #12
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    Hello, is anyone answering??

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Extract Highlighted in red

    Hello Jerry HKA,

    Here is the revised macro. This will keep the leading zeroes. If the cell color is set using Conditional Formatting, the macro will not recognize it. I had no trouble detecting triple repeating digits.
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor Jerry HKA's Avatar
    Join Date
    09-25-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    126

    Re: Extract Highlighted in red

    Oh, thank you for your help, I think there should be not any more errors.
    Last edited by Jerry HKA; 10-06-2012 at 01:54 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1