Find out values difference between 2 excel sheet

FirstLog 2 Posts Wednesday June 14, 2017Registration date June 19, 2017 Last seen - Jun 19, 2017 at 04:12 AM - Latest reply: TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen
- Jun 19, 2017 at 11:12 AM
Hello,


Dear Friends,

I have facing a big problem in my generate billing per month because i have 2 excel sheet 1st one create manually (Manual) day by day entry and another one is software generated (Software), big problem is now that how to find difference in entry more than 10,000 row for a months.
i have common field in both sheet are Airway Bill (Unique Key), Pcs, Weight, Volume Weight and Amount... now find out NOT MATCHED entry.

Please help me for solved this problem.
Thanks in advance for your solutions.

Vineet Sharma
See more 

Your reply

1 reply

TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Jun 19, 2017 at 11:12 AM
0
Helpful
Hi Firstlog,

So you have 2 sheets both with the same headers (6 in total) where column A contains unique value.

The code below loops through all the rows in the Manual sheet. It will look for the unique value in column A of the Software sheet. Once found, it will check if all the 5 subsequent columns contain the same data. If not, then the cells in the Manual sheet will be colored red.

Hopefully this is what you are after.

Here is the code:
Sub RunMe()
Dim mFind As Range

Sheets("Manual").Select

For Each cell In Range("A2:A" & Range("A2").End(xlDown).Row)
    Set mFind = Sheets("Software").Columns("A").Find(cell.Value)
    If Not mFind Is Nothing Then
        If mFind.Offset(0, 1) <> cell.Offset(0, 1) Or _
        mFind.Offset(0, 2) <> cell.Offset(0, 2) Or _
        mFind.Offset(0, 3) <> cell.Offset(0, 3) Or _
        mFind.Offset(0, 4) <> cell.Offset(0, 4) Or _
        mFind.Offset(0, 5) <> cell.Offset(0, 5) Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex=3
        End If
    End If
Next cell

End Sub
Respond to TrowaD