comparing 2 workbooks and highlighting differences

[Guest]

Not sure if this Q should be in programming or Worksheets functions. There
are 2 systems, (for double checking purposes) inputting data. The master
system will save the file as master.xls. The 2nd system will save their
file as compare.xls. It will then be compared to the master.xls for
accuracy, comparing the entries of each cell against each other in a
specific column. (The total column)

So for instance, master.xls, Sheet 1, range F10, should be the same as
compare.xls, sheet 1 range F10. If it is not the same I would like it to be
highlighted in say red, on the master sheet

I have 12 separate sheets in the workbooks, and am looking for a simple way
of achieving this, instead of just using the compare side by side on the
toolbar, which I don’t think would highlight any differences. I have looked
at compare.xla, but dont really know what I should be doing with it now I
have downloaded it, any help please

Be grateful .
Thank you

Sybs

 

[Norman Jones]

Hi Sybs,

Try the Compare addin, developed by Myrna Larson
and Bill Manville, which may be downloaded, free of
charge, from Chip Pearson's Downloads page at:

http://www.cpearson.com/excel/download.htm

 

[Guest]

thanks Norman. I did go there having searched through the posts, but
unfortunately, I have no idea how I am supposed to use it now I have
downloaded it. Help ?

sybs

 

[Norman Jones]

Hi Sybs,

The Compare.xla adds a menu item 'Compare worksheets..'
at the foot of Excel's tools menu.

Select the menu item and the remainder should be
self-explanatory.

 

[Guest]

I have downloaded the compare.xla file and there is sa compare worksheets
menu item onthe tools men. When i select it it gives a message saying did
not find any worksheets. I am obviusly totally doing something wrong. Sorry
to be a pain. Do I need to install this somewhere special or how does it work.

Sorry to be thick.

 

[Norman Jones]

Hi Sybs,

Select the Compare menu option on the Tools menu
Select a sheet from the first dropdown list
Select a sheet from the second dropdown
Select one of the three value \ formula options
OK

The addin creates a report sheet, listing the
differences.

 

[Guest]

Hi,

It obviously does exactly what I am hoping to do, I have followed your
instructions, but it gives me a message box saying did not find any
worksheets. have both books open, I just dont know what I am doing wrong.

 

[Guest]

sorry Norman,

I have just produced 2 more books to try with and it works fine. I didnt
realise that it wouldnt work with a protected sheet, which both the workbooks
I was trying to compare have protection.

Thank you for your help with this, I am sorry I took so long to get it, but
the add in didn't tell me why it couldn't find any worksheets !

Thanks again.

Sybs

 

[Norman Jones]

Hi Sybs,

'------------------
It obviously does exactly what I am hoping to do, I have followed your
instructions, but it gives me a message box saying did not find any
worksheets. have both books open, I just dont know what I am doing wrong.
'------------------

I cannot reproduce your problem; I have never
previously encountered any problem with the addin,
nor can I recall reading of a similar experience.

I can only suggest that you try deleting the addin file
and re-download it.

Incidentally, I have tried a fresh download, which
works as expected.

 

[ryguy7272]

This is a great macro for comparing data in two sheets:
Sub FindDupes() 'assuming both sheets are in same book and book is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)


sht1.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht1 = ActiveCell.Row

sht2.Activate
sht2.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht2 = ActiveCell.Row

sht1.Activate
For rowSht1 = 1 To LastRowSht1
If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
For rowSht2 = 1 To LastRowSht2
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value
Then
sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3
sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3

End If
Next
Next
sht1.Cells(1, 1).Select
End Sub

Regards,
Ryan--