Announcement

Collapse
No announcement yet.

Compare two worksheets and highlight the one sheet’s difference from the other one

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Compare two worksheets and highlight the one sheet’s difference from the other one

    Does anyone know how to do this? Thank you for your great help!

  • #2
    Re: Compare two worksheets and highlight the one sheet’s difference from the other one

    THis code will compare all cells of sheet1 with sheet 2 and post diferences in sheet three. If you want to limit the area of sheet1 compared to sheet two then change the range.

    Code:
    Sub CompareWorkSheets()
    Dim c1 As Range
    Dim c2 As Range
    Dim RowPos As Long
    
    Sheet3.Range("A1") = "Row"
    Sheet3.Range("B1") = "Column"
    Sheet3.Range("C1") = "Sheet1"
    Sheet3.Range("D1") = "Sheet2"
    
    RowPos = 1
    
    For Each c1 In Sheet1.Range("A1:IV65535")  '<<<Change range as required
    
    If c1 <> Sheet2.Cells(c1.Row, c1.Column) Then
    
        'Record difference
        RowPos = RowPos + 1
        Sheet3.Cells(RowPos, 1) = c1.Row
        Sheet3.Cells(RowPos, 2) = c1.Column
        Sheet3.Cells(RowPos, 3) = c1
        Sheet3.Cells(RowPos, 4) = Sheet2.Cells(c1.Row, c1.Column)
        MsgBox "Found"
    End If
    Next c1
    
    End Sub
    Bill
    Bill
    Tip: To avoid chasing code always use Option Explicit.

    Comment


    • #3
      Re: Compare two worksheets and highlight the one sheet’s difference from the other one

      Note that the code I previously posted does not check for diffreneces in formulas.

      Bill
      Bill
      Tip: To avoid chasing code always use Option Explicit.

      Comment


      • #4
        Re: Compare two worksheets and highlight the one sheet’s difference from the other one

        Thank you so much. Bill! it works

        Comment

        Working...
        X