Validate Data of sheet1 & sheet2 and mismatch data come in sheet3

[MohitMathur1234]

I have data in sheet1 and sheet2 and both have almost same data but somehow data is mismatch so I want it will highlight in sheet3. Like which cells value is diff from sheet1.

Sheet1
a1 b1 c1 d1
1 2 3 4
2 3 3 4

sheet2
a1 b1 c1 d1
1 3 4 4
2 3 3 4

here, b1 & c1 data is diff in sheet1, so these data should be come in sheet3 .

I have lot of data, above is only a example.

 

[Chihiro]

Why create additional sheet? Highlighting cells with different values from sheet1 in sheet2 should make it easier to fix or compare visually.

Assuming Sheet1 & Sheet2 has identical format

Sub Compare()

Call compareSheets("Sheet1", "Sheet2")

End Sub

Sub compareSheets(shtOriginal As String, shtCompare As String)
Dim mCell As Range
Dim mDiffs As Integer

For Each mCell In ActiveWorkbook.Worksheets(shtCompare).UsedRange
    If Not mCell.Value = ActiveWorkbook.Worksheets(shtOriginal).Cells(mCell.Row, mCell.Column).Value Then
        mCell.Interior.Color = vbYellow
        mDiffs = mDiffs + 1
    End If
Next

MsgBox mDiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtCompare).Select

End Sub

Also, this link may help.
http://chandoo.org/wp/2014/12/15/compare-data-highlight-mismatched-letters-words/

 

[MohitMathur1234]

It's a data for audit and I need to check the mistake of employees who do this audit.
I don't want to tell them their mistake. If data will color so I can't know that where was the mistake
As employee will rectify on their end. That's why I need to fetch mistake in 3 sheet.

 

[Chihiro]

Ok then, you can just copy Sheet2 to Sheet 3 and then modify above code to use "Sheet3" instead of "Sheet2" to check.

Code to copy

Sub Copy()

ActiveWorkbook.Sheets("Sheet2").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet3").Select
ActiveWorkbook.Sheets("Sheet3").Range("A1").Select
ActiveWorkbook.Sheets("Sheet3").Paste
End Sub

 

[MohitMathur1234]

It's not been a fully helpful code. I am unable to do actually what I want.
I want, If I compare sheet 1 and sheet 2 so difference reflect in sheet 3. As per above code reflecting in sheet 2.
Please help..!!

 

[Chihiro]

Change this part

Sub Compare()

Call compareSheets("Sheet1", "Sheet2")

End Sub

TO

Sub Compare()

Call compareSheets("Sheet1", "Sheet3")

End Sub

 

[MohitMathur1234]

Ohh......Thanks a lot.It's working..Haha