Ndtyjky

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.