Myujth

I have 2 sheets with one offline data and one online data from a DB server and want to sort the difference between the two sheets and highlight the difference and count the difference and display the counting value in the end column if its there else have to say "same" in the end column.
For eg: Sheet 1

 A B C D 
 T1 T2 T3 T4 
 T1 T2 T3 T4

Sheet 2

 A B C D 
 T1 T2 T3 T4 
 T1 T2 T4 T5 

Result--> sheet 3 should be

 A B C D E 
 T1 T2 T3 T4 
 T1 T2 T3 T4 Same 
 T1 T2 T3 T4 
 T1 T2 T4 T5 2 difference

The Code i have is highlighting the difference but not Pasting the Sheet 1 value in the Sheet 3.My end result will be comparing the Same row and cell between the Sheet1 snd Sheet2 and have to paste the difference in the sheet 3 with pasting both rows consecutively. Any help is highly appreciated.

Sub Compare()
Dim ColumnCount, RowCount As Long
 Dim w As Worksheet, r As Range

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

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

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

For i = 1 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("Sheet4").Cells(i, j).Value = Sheets("Sheet1").Cells(i, j).Value 'Copying the Header of the Mismatched Cell
 Sheets("Sheet4").Cells(i, j).Value = Sheets("Sheet2").Cells(i, j).Value 'CStr("MisMatch") 'If mismatch setting set value as MisMatch
 Sheets("Sheet4").Cells(i, j).Interior.Color = 65535 'Highlighting with Yellow color
 Else
 Sheets("Sheet4").Cells(i, j).Value = Sheets("Sheet2").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
MsgBox "SORTINGCOMPLETE"
End Sub

I think it would be better and easier to have all data in same sheet, because you are comparing always same rows of both sheets. So I did this:

You compare row 2 of Sheet1 with row 2 of Sheet2, row3 with row 3, and so on...

Then,in the evaluation column, I have this formula:

=IF(4-SUMPRODUCT(--(A2:D2=F2:I2))>0;4-SUMPRODUCT(--(A2:D2=F2:I2))&" difference";"same")

As you can see, first comparison returns same because there are no differences. Second comparison (row 3) returns 2 difference because there is only 2 matches.

To highlight the evaluation, just a conditional formatting rule. If the text contains the word difference, highlit it.

Hope you can adap this to your needs.