Compare two identical large workbooks changes

[Lambrix]

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?

 

[AvcGJS]

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]

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)

Right?

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

 

[AvcGJS]

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]

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]

bit like this maybe... depends what it looks like at your end, something to work from anyway...

' 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]

bit like this maybe... depends what it looks like at your end, something to work from anyway...

' 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