# Compare two identical large workbooks changes

#### Lambrix

##### Board Regular
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
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
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

##### Board Regular
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

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

Replies
1
Views
53
Replies
0
Views
212
Replies
2
Views
132
Replies
4
Views
175
Replies
0
Views
48

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.

### Which adblocker are you using?

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

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