Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Compare Two Sheets and Highlight Differences




    I found the code in a post and it is excellent for what I am trying to achieve.
    However, in my spreadsheets I only want to compare the first 5 (five) columns.

    How can this code be altered to do this?

    Any assistance would be most appreciated.


    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(shtSheet2).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).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(shtSheet2).Select

    End Sub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lot of ways to do this but some are faster than others.

    HTH
    Maud

    Code:
    Sub RunCompare()
         Call compareSheets("Sheet1", "Sheet2")
    End Sub
    
    Sub compareSheets(shtSheet1 As String, shtSheet2 As String)
    '--------------------------------
    'DECLARE AND SET VARIABLES
        Dim ws1 As Worksheet, ws2 As Worksheet
        Dim Cell As Range, Col As Range, mycell As Range
        Dim mydiffs As Integer
        Set ws1 = Worksheets(shtSheet1)
        Set ws2 = Worksheets(shtSheet2)
    '--------------------------------
    'CYCLE THROUGH COLUMNS
        For Each Col In ws2.Columns("A:E")
    '--------------------------------
    'CYCLE THROUGH ONLY USED CELLS IN COLUMN
            LastRow = ActiveSheet.Cells(Rows.Count, Col.Column).End(xlUp).Row
            For Each Cell In Range(Cells(1, Col.Column), Cells(LastRow, Col.Column))
                If Not Cell = ws1.Cells(Cell.Row, Cell.Column).Value Then
                   Cell.Interior.Color = vbYellow
                   mydiffs = mydiffs + 1
                End If
            Next
        Next
    '--------------------------------
    'DISPLAY A MESSAGE BOX TO DEMONSTRATE THE DIFFERENCES
        MsgBox mydiffs & " differences found", vbInformation
        ActiveWorkbook.Sheets(shtSheet2).Select
    End Sub
    What if there was a cell on sheet1 with a value and the corresponding cell on sheet 2 is blank. Should that be highlighted?
    Last edited by Maudibe; 2016-06-27 at 17:52.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi Maud
    Thankyou and sorry for the delay in responding. The response is "Yes" this should be highlighted.

Posting Permissions

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