Results 1 to 2 of 2

Compare to sheets

Threaded View

  1. #1
    Registered User
    Join Date
    01-17-2017
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    16

    Compare to sheets

    I need to compare two sheets. I have following code:

    Sub RunCompare()
    
    Call compareSheets("Sheet1", "Sheet2")
    
    End Sub
    
    
    Sub compareSheets(shtSheet1 As String, shtSheet2 As String)
    
    Dim mycell As Range
    Dim mydiffs As Integer
    
    'For each cell in sheet2 that is not the same in Sheet1, color it yellow
    For Each mycell In ActiveWorkbook.Worksheets(shtSheet1).UsedRange
        If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet2).Cells(mycell.row, mycell.Column).Value Then
            
            mycell.Interior.Color = vbYellow
            mydiffs = mydiffs + 1
            
        End If
    Next
    
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " differences found", vbInformation
    
    ActiveWorkbook.Sheets(shtSheet1).Select
    
    End Sub
    Everything works, but I found one problem: In the sheet1 I do have some rows which are not in sheet2. I need to find those rows and highlight them. That means I have to modify this VBA code following: work with entire row, not with cell and find out, if every row from sheet1 is present in sheet2, if not, highlight entire row if yes, delete this row from sheet1.... but at the same time those rows from sheet1 which are in sheet2 but only one or few value in the row are different, highlight that difference and if the entire row is the same as in sheet2, delete it....that means I will get in sheet1 those rows where are different values, and those rows which are not in sheet2

    For example:
    Values in Sheet1
    A124 335 403 409 436 579 4129
    A124 355 403 409 432 350 3989
    A124 375 401 407 430 80 3610
    A124 395 401 407 430 0 3580
    A124 999 401 407 430 0 3579


    Values in Sheet2
    A124 335 403 409 436 579 4129
    A124 355 403 409 432 350 3989
    A124 375 301 407 430 80 3610

    Final sheet1
    A124 375 401 407 430 80 3610
    A124 395 401 407 430 0 3580
    A124 999 401 407 430 0 3579

    In Final Sheet, we will keep only those rows where are differences, in the first row, there will be highlighted only value 401.00 because in sheet 2 the value is different and the second and third row, there will be highlighted entire row because those rows are not in sheet2.

    !!!Note:If I want to identify if the rows from sheet1 is in sheet2, it is enough to work just with combination of first two columns in every rows (those have to be always the same in both sheets, if they are not, the row is missing)....the other columns are for values, which can be different and have to find those differences.

    Is it possible to write macro even for this complicated case??? If you could you please help me?
    Last edited by mgecelov; 03-29-2017 at 11:33 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compare two sheets using VBA & populate the results in new sheets
    By jhonnyexcel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-24-2016, 10:46 AM
  2. Replies: 1
    Last Post: 07-23-2015, 10:15 PM
  3. Compare Sheets
    By kannan1847 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2015, 11:20 AM
  4. [SOLVED] Compare Two Value in Different Sheets
    By gingank in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-21-2012, 11:01 PM
  5. Compare Two Sheets
    By mrm4c in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2009, 10:26 AM
  6. Excel 2007 : compare sheets
    By rajeshkumawat in forum Excel General
    Replies: 1
    Last Post: 07-05-2009, 01:33 PM
  7. Compare two sheets
    By Dhruva101 in forum Excel General
    Replies: 0
    Last Post: 06-04-2007, 04:06 PM

Tags for this Thread

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