+ Reply to Thread
Results 1 to 7 of 7

Comparing data on 2 sheets (varying rows) and highlight differences in 3rd

  1. #1
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Comparing data on 2 sheets (varying rows) and highlight differences in 3rd

    Hi all,

    I'm hunting for some code which would do a simple compare of data in 2 sheets within the same workbook and then highlight differences in sheet 3 (based on sheet 1 data used as a base).

    The macro would copy sheet 1 and paste it in sheet 3 and then compare to sheet 2 with any differences highlighted in yellow.

    Data in both sheets have the same format and column headings but the rows would vary.
    Column A contains a unique reference number which i thought could be used as the identifier between the 2 sheets.

    I've attached a basic test file which may be useful.




    Below is some code which compares 2 columns in the same sheet and highlights differences. I was hoping it could possibly be modified/expanded on to apply to above scenario.


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Gti182; 05-15-2014 at 04:42 AM.

  2. #2
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: Comparing data on 2 sheets and highlight differences in 3rd

    how about using conditional formatting ?

    Use a formula to determine which cells to formate
    Please Login or Register  to view this content.
    Applies to
    Please Login or Register  to view this content.
    or here's a macro that will copy sheet 2 and apply the conditional formatting to the new sheet

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Comparing data on 2 sheets and highlight differences in 3rd

    Thanks ThirtyTwo, thought about conditional formatting but ideally i'd like to avoid this route if possible as there would be a lot of data to compare and the file is large and slow as it is. The users would also need the ability to format the compare sheet as they choose without having to worry about removing conditional formatting again.

    Your code doesn't seem to highlight the required cells? also it looks like its copying sheet 2 rather than sheet 1

  4. #4
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Comparing data on 2 sheets and highlight differences in 3rd

    bump anybody else?

  5. #5
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Comparing data on 2 sheets (varying rows) and highlight differences in 3rd


  6. #6
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Comparing data on 2 sheets (varying rows) and highlight differences in 3rd

    nobody?

  7. #7
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: Comparing data on 2 sheets (varying rows) and highlight differences in 3rd

    up please

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. highlight differences of data on 2 sheets
    By steve111 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-04-2014, 11:21 AM
  2. Replies: 1
    Last Post: 11-08-2013, 08:54 AM
  3. Comparing two sheets and highlight unmatched data
    By vignesh rocks in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2012, 02:05 PM
  4. Compare two Sheets - Add New Data, Update Differences, Highlight Void Data
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-18-2010, 04:03 AM
  5. Quick method of comparing two columns and highlight differences
    By PRodgers4284 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2010, 11:03 AM

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