Previous Topic Next Topic

Find out values difference between 2 excel sheet

Closed
FirstLog
Posts
2
Registration date
Wednesday June 14, 2017
Status
Member
Last seen
June 19, 2017
 - Jun 19, 2017 at 04:12 AM
TrowaD
Posts
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
 - 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

1 reply

Answer 1 / 1
TrowaD
Posts
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
 515
Jun 19, 2017 at 11:12 AM
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
0