Macro search for text and highlight row

  • Hi all,


    I'm refining the code on a macro I posted a couple of weeks ago, and one of the issues I'm encountering is how to have my macro highlight a row in red for the text phrase "Winery Direct", when the cell contains more than just these two words. Unfortunately, I'm at the mercy of the sales team who sends me the data file, so sometimes they will send the file and the columns containing only text, but at other times they'll send it with text and dates mixed in this column. The purpose for highlighting this row is so that I can capture it and delete it before importing into my financial software since a Winery Direct has already been created and so this line would create a duplicate record. Make sense?


    I know that when the cell contains only "Winery Direct", the highlighting works, but when it contains mixed data, it doesn't so I'm certain I need to add something like a string for contains, but I'm not advanced enough in VBA to know how to code for that. Any advice or pointers is most welcome.


    Karen Stone
    Staff Accountant
    ZD Wines

  • Re: Macro search for text and highlight row


    Ah ha! Yes, that is exactly what I was trying to wrap my brain around. This worked perfectly. Thanks for all your help - it's much appreciated.

    Karen Stone
    Staff Accountant
    ZD Wines

  • Re: Macro search for text and highlight row


    I see cytop has beaten me to the punch!! Nonetheless here's what I came up with:



    I must ask - why do you highlight the rows only then to delete them? Why not just delete them to start with?


    Regards,


    Robert

  • Re: Macro search for text and highlight row


    The reason for highlighting the rows is so that I can find them to delete them. The spreadsheet that I receive is from my external sales team, so the report is often very long and, frankly, I just don't feel like scanning a report for rows that I need to delete when I can have the macro, which is already running to find fields that I need to edit to correct misspellings, etc. also highlight rows that are going to create duplicate records. The spreadsheet ultimately gets imported into QuickBooks and the wizards at Intuit are aware that QuickBooks has no duplicate record filter for excel imports, nor do they plan on creating one, so this is the work around I've had to develop.

    Karen Stone
    Staff Accountant
    ZD Wines

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!