VBA code that returns the results of macro in a MsgBox

[KrystynaD]

At the end of running my macro I would really like it if a message box appeared displaying the results of the macro.
The message box would say "X" number discrepancies were identified, or "0" discrepancies were identified.
I am having great difficulty making this happen, i would really appreciate the help.

Please see my code below..

- This code compares the data in Sheet1 and Sheet 2 looking for any discrepancies.
- The results from this macro are then returned in Sheet 3.
- I have attached 3 images. Sheet1 and Sheet2 are the data being compared and sheet 3 is the result of the comparisons - showing any discrepancy.

Sub LookForDiscrepancies()
    Dim varS1, varS2, varH1, varH2
    Dim rngS1 As Range, rngS2 As Range
    Dim c As Range, c1 As Range, c2 As Range
    Dim iRow As Integer, iCol As Integer, i As Integer, iTest As Integer
    
    Sheet1.Activate
    Set rngS1 = Intersect(Sheet1.UsedRange, Columns("A"))
    Sheet2.Activate
    Set rngS2 = Intersect(Sheet2.UsedRange, Columns("A"))
    Sheet3.Activate
    Sheet3.Cells.Select
    Ans = MsgBox(prompt:="Do you really want to delete data in Sheet3?", _
Buttons:=vbYesNo + vbExclamation, _
Title:="Delete Data!")

If Ans = vbNo Then Exit Sub ' vbNo = 7, vbYes = 6


    Selection.Delete Shift:=xlUp
    Sheet3.Rows("1:1").Value = Sheet1.Rows("1:1").Value
    
    Let iRow = iRow + 2
    With rngS2
         'Search for Sheet1 IDs on Sheet2
        For Each c1 In rngS1
            On Error GoTo 0
            Set c = .Find(what:=c1.Value) 'Look for match
            If c Is Nothing Then 'Copy the ID to Sheet3
                Sheet3.Cells(iRow, 1) = c1
                Sheet3.Cells(iRow, 2) = "exist in sheet 1 not in sheet 2"
                Let iRow = iRow + 1
                Else 'Check if rows are identical
                Let varS1 = Intersect(Sheet1.UsedRange, c1.EntireRow)
                Let varS2 = Intersect(Sheet2.UsedRange, c.EntireRow)
                Let iCol = Intersect(Sheet1.UsedRange, c1.EntireRow).Count
                ReDim varH1(1 To iCol) As Integer
                For i = 1 To iCol
                    If Not varS1(1, i) = varS2(1, i) Then
                        Let iTest = iTest + 1
                        Let varH1(i) = 1
                    End If
                Next i
                If iTest Then 'Rows are not identical
                    For i = 1 To iCol
                        Sheet3.Cells(iRow, i) = varS1(1, i)
                        If Not varH1(i) = 0 Then Cells(iRow, i) _
                        .Interior.ColorIndex = 40
                    Next i
                    Let iTest = 0
                    Let iRow = iRow + 1
                End If
            End If
        Next
    End With
    
    Let iRow = iRow + 0
    Range("A1").Offset(iRow, 0).Value = "Sheet2 vs Sheet1"
    Let iRow = iRow + 2
        With rngS1
         'Search for Sheet2 IDs on Sheet1
        For Each c2 In rngS2
            On Error GoTo 0
            Set c = .Find(what:=c2.Value) 'Look for match
            If c Is Nothing Then 'Copy the ID to Sheet3
                Sheet3.Cells(iRow, 1) = c2
                Sheet3.Cells(iRow, 2) = "exist in sheet 2 not in sheet 1"
                Let iRow = iRow + 1
                Else 'Check if rows are identical
                Let varS1 = Intersect(Sheet2.UsedRange, c2.EntireRow)
                Let varS2 = Intersect(Sheet1.UsedRange, c.EntireRow)
                Let iCol = Intersect(Sheet2.UsedRange, c2.EntireRow).Count
                ReDim varH2(1 To iCol) As Integer
                For i = 1 To iCol
                    If Not varS1(1, i) = varS2(1, i) Then
                        Let iTest = iTest + 1
                        Let varH2(i) = 1
                    End If
                Next i
                If iTest Then 'Rows are not identical
                    For i = 1 To iCol
                        Sheet3.Cells(iRow, i) = varS1(1, i)
                        If Not varH2(i) = 0 Then Cells(iRow, i) _
                        .Interior.ColorIndex = 36
                        Next i
                    Let iTest = 0
                    Let iRow = iRow + 1
                    End If
            End If
        Next
    End With
     Sheet3.Select 'resize the columns
     Range("A:Z").Columns.AutoFit
         
End Sub

 

[KrystynaD]

I've just tried using the debugger to set a breakpoint and the macro still keeps returning the value of all the cells with data under the title Sheet2 vs Sheet1.
i'm really not sure why that is. I played around with the DCnt = DCnt + 1 function, when i did DCnt = DCnt + 2 it returned back double the result, and when i did 0 and 0/5 it returned with the message no discrepancies.

do you think there is something i am missing?

 

[rlv01]

I think this should fix it. Replace this section:

For i = 1 To iCol
    Sheet3.Cells(iRow, i) = varS1(1, i)
    If Not varH2(i) = 0 Then Cells(iRow, i) _
    .Interior.ColorIndex = 36
    DCnt = DCnt + 1                                   'you said the yellow cells are the discrepancies, so let's count the yellow cells.
Next i

with this:

For i = 1 To iCol
    Sheet3.Cells(iRow, i) = varS1(1, i)
    If Not varH2(i) = 0 Then
        Cells(iRow, i).Interior.ColorIndex = 36
        DCnt = DCnt + 1                               'you said the yellow cells are the discrepancies, so let's count the yellow cells.
    End If
Next i

 

[KrystynaD]

Yay! That change did everything, the new VBA code works beautifully (peep the image)!! Thank you so much for your time and effort, you have really saved me.