Compare two excel workbooks

[mattwellings]

Hi Guys, I have got a problem.

I am trying to compare two different excel workbooks by using a vbs script, the current script below is okay as it compares them.

But the issue is when a row has been shifted down, the script thinks that the data has changed and therefore highlights the row as red, but the data has only been moved down.

I have downloaded a programe called "Excel Compare" but as it is for a buisness i will need to buy a licese. What "Excel Compare" does is tell's you what data has been added and what data has been removed.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open("C:\Users\")
Set objWorkbook2= objExcel.Workbooks.Open("C:\Users\")
msgbox"The comparison started!",64, "Excel Comparison"
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
   For Each cell In objWorksheet2.UsedRange
       If cell.value <> objWorksheet1.Range(cell.Address).Value Then
           cell.Interior.ColorIndex = 3
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next
msgbox"The comparison has now complete!",64,"Excel Comparison"
set objExcel=nothing

Any help would be greatly appreciated.

Thanks

 

[CharlesChuckieCharles]

Your comparison is working correctly if you want both sheets the same then you have to make the same changes to both

if you want to compare the data then you have to use a method of keying between the two sets

 

[mattwellings]

The thing is i dont want them to be the same, there will be different data being added, and what i want is for the sript to tell me what data is new and what has been removed.

How do i do the method about keying between the two sets ?

Thanks for your response.