Compare two identical large workbooks changes

Lambrix

Board Regular
Joined
Jun 29, 2012
Messages
62
Good morning,

I have two excel workbooks formatted exactly the same. Is there a way that I can compare the two documents and highlight the cells with changes throughout the entire workbook?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AvcGJS

Board Regular
Joined
May 15, 2009
Messages
88
I had a similar one once - two staff rotas and we needed to spot the difference I put in a 3rd sheet
Then used the IF formula thus

=IF(Sheet1!A2=Sheet2!A2,1,0)

Then had a macro colour all the ones that had changed (the 0 ones) on the third sheet and then copy and paste that formatting over the 1st sheet - thus highlighting all differences between sheet 1 and sheet 2
 

Lambrix

Board Regular
Joined
Jun 29, 2012
Messages
62
I had a similar one once - two staff rotas and we needed to spot the difference I put in a 3rd sheet
Then used the IF formula thus

=IF(Sheet1!A2=Sheet2!A2,1,0)

Then had a macro colour all the ones that had changed (the 0 ones) on the third sheet and then copy and paste that formatting over the 1st sheet - thus highlighting all differences between sheet 1 and sheet 2

I am trying to understand how you set up the formula. So I have consolidated the sheets into one workbook and gave them separate names, then created a copy that I am using to plug in the formula above and named it Comparison

National1!
National2!
Comparison!

So the formula to use would look like this in my case:

=IF(National1!A2=National2!A2,1,0) :confused:

Right?

Where do I input the formula on the 3rd sheet?
 

AvcGJS

Board Regular
Joined
May 15, 2009
Messages
88
Right :)

put the formula on the third sheet in cell A2 as well -

Think of it like two pieces of paper and piece of glass (comparison) - once comparision is coloured in you can put it over national 1 and 2 and see differences - so long as its all lined up.
 

Lambrix

Board Regular
Joined
Jun 29, 2012
Messages
62

ADVERTISEMENT

Right :)

put the formula on the third sheet in cell A2 as well -

Think of it like two pieces of paper and piece of glass (comparison) - once comparision is coloured in you can put it over national 1 and 2 and see differences - so long as its all lined up.

Awesome! It worked. I am now trying to put together a macro that will highlight all the "0" values.
 

AvcGJS

Board Regular
Joined
May 15, 2009
Messages
88
bit like this maybe... depends what it looks like at your end, something to work from anyway...:cool:

' Macro39 Macro
'


'Go through row by row and colour in....


Range("A1").Select




Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

If ActiveCell.Value = 0 Then ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = 1 Then ActiveCell.Offset(1, 0).Select

End If
Loop

Range("b1").Select




Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

If ActiveCell.Value = 0 Then ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = 1 Then ActiveCell.Offset(1, 0).Select

End If
Loop


End Sub
 

Lambrix

Board Regular
Joined
Jun 29, 2012
Messages
62
bit like this maybe... depends what it looks like at your end, something to work from anyway...:cool:

' Macro39 Macro
'


'Go through row by row and colour in....


Range("A1").Select




Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

If ActiveCell.Value = 0 Then ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = 1 Then ActiveCell.Offset(1, 0).Select

End If
Loop

Range("b1").Select




Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

If ActiveCell.Value = 0 Then ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = 1 Then ActiveCell.Offset(1, 0).Select

End If
Loop


End Sub

Thanks! That worked like a charm. I really appreciate it
 

Forum statistics

Threads
1,141,483
Messages
5,706,649
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top