+ Reply to Thread
Results 1 to 10 of 10

Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique ID

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    Nevada, Iowa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique ID

    I know this is a relatively simple macro, and surely this has been answered before but I cannot locate it. But, Schedule Compare Tool.xlsmI'm struggling and now wasting more time then making progress.

    In a nut shell, I have two sheets that need comparing (Old to Current). It first needs to find the exact match in Column A (ID). Then compare all the follow on data (Columns B-F) for that Unique ID & highlight the differences on sheet 1 (Current). If no match to Unique ID is found in either sheet, it should highlight that ID a different color.

    Please help! THANK YOU SO VERY MUCH!

  2. #2
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    sounds like a simple vlookup formula???

    will look at it later

  3. #3
    Registered User
    Join Date
    04-18-2013
    Location
    Nevada, Iowa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    Everything sounds simple...unless I have to try to do it! :-) THANK YOU!! Any help is MUCH appreciated.

  4. #4
    Registered User
    Join Date
    04-18-2013
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    Hi,

    I don't think you need a macro to do this... if I understand what you want you should be able to do this with 2 conditional formatting statements and a few vlookup & match functions.

    Take a look at the attached and see if this is what you're looking for...

    Best regards,
    Mike
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-18-2013
    Location
    Nevada, Iowa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    I suggested the macro because I will use this over and over, daily with new data blown in to make updates from (same fields/columns, just new info in those fields). This does appear to work (YOU"RE AWESOME!), I just worry I'll break the formula when inputting (usually copy/paste) the new data. Thoughts?

  6. #6
    Registered User
    Join Date
    04-18-2013
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    oh ok, in that case what you want to do is just expand the range for both the vlookup functions and the conditional formatting "applies to" range to something that will cover the amount of data you would put in... pick something like 10,000 rows and make sure the formulas in columns H to K go down that far.

    Hope that helps!

  7. #7
    Registered User
    Join Date
    04-18-2013
    Location
    Nevada, Iowa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    Ok, I'll give that a try. THANKS!!!!

  8. #8
    Registered User
    Join Date
    04-18-2013
    Location
    Nevada, Iowa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    It loses the conditional formatting when a new set of data is pasted in...or I broke the formula when I changed the range. Where's the highlight function stored? In the formula or the cells, so if I paste overtop, am I clearing that Cond. Formatting?

  9. #9
    Registered User
    Join Date
    04-18-2013
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    Ok here take a look at this. i've made a place for you to paste the data you have...up to 1000 rows. The other sheets read off of that. Take a look at the formula's in the conditional formating>manage rules... choose this worksheet, and you'll see how the formulas in here are basically like an if statement that control the formatting. Pay close attention to the hierarchy of the 3 conditional formatting formulas.

    Play around with it and let me know if you have any questions.

    br,
    Mike
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-18-2013
    Location
    Nevada, Iowa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique

    THANK YOU SO MUCH!! I'll Try it out and dump some new data in it tomorrow as well! I'll let you know.

+ 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