Compare two data sets in different sheet and print difference in other sheet using excel VBA?

0
votes

I have two data sets in two different sheet Sheet1 is my Orginal ref and sheet2 is for comparison. sheet2 data should get compared by Sheet1 and print entire mismatched row of sheet2 and highlight the cells which has mismatch data and this difference should be printed with column header in other specified Sheet and in specified range

Also mismatch cell count should be updated in any sheet3 specified range cell

Below is the tried code. Any help will be appreciated.

Sub CompareDataSet()
Call compareSheets("Sheet1", "Sheet2")
End Sub
Sub compareSheets(Sheet1 As String, Sheet2 As String)
Dim Cell As Range
Dim CellMisMatch As Integer
For Each Cell In ActiveWorkbook.Worksheets(Sheet1).UsedRange
If Not Cell.Value = ActiveWorkbook.Worksheets(Sheet2).Cells(Cell.Row, Cell.Column).Value Then
Let Worksheets("Sheet3").Cells(Cell.Row, Cell.Column) = Cell
Cell.Interior.Color = vbYellow
CellMisMatch = CellMisMatch + 1
End If
Next
ThisWorkbook.Sheets("Sheet3").Cells(1, 1).Value = CellMisMatch
End Sub
1
excelvba

1 Answers

1
votes

Here is the code that will compare sheet1 and sheet2(corresponding cells ) and according wite the correct value or Mismatch based upon the result into sheet3. Sheet1 and sheet2 wil have same number of rows and columns and the headers be same so you can keep them as it is in sheet3. Hope it helps.

Sub Compare()

'Clearing the contents of the third sheet for the fresh comparison

usedCoulms = Sheets("Sheet3").UsedRange.Columns.Count
usedRows = Sheets("Sheet3").UsedRange.Rows.Count
For i = 2 To usedRows
For j = 1 To usedCoulms
   Sheets("Sheet3").Cells(i, j).Value = ""
   Sheets("Sheet3").Cells(i, j).Interior.Color = RGB(255, 255, 255)
Next
Next

'Coulmn count of first sheet
ColumnCount = Sheets("Sheet1").UsedRange.Columns.Count
'row count of first sheet
RowCount = Sheets("Sheet1").UsedRange.Rows.Count

For i = 2 To RowCount
For j = 1 To ColumnCount
    If Sheets("Sheet1").Cells(i, j).Value <> Sheets("Sheet2").Cells(i, j).Value Then    'Comparing if values are not equal
        Sheets("Sheet3").Cells(1, j).Value = Sheets("Sheet1").Cells(1, j).Value 'Copying the Header of the Mismatched Cell
        Sheets("Sheet3").Cells(i, j).Value = CStr("MisMatch")   'If mismatch setting set value as MisMatch
        Sheets("Sheet3").Cells(i, j).Interior.Color = 65535     'Highlighting with Yellow color

    Else
        Sheets("Sheet3").Cells(i, j).Value = Sheets("Sheet1").Cells(i, j).Value
        'If values are same copy the first sheets value if dont want to copy can skip this
    End If
Next
Next

End Sub