How to compare two versions of a workbook

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

How to compare two versions of a workbook

Post by StuartR »

I have a workbook with just one worksheet.
I need to highlight cells where the two workbooks are different.

All the options for doing this that I can find on a web search seem to use Excel features or add-ons that I don't have.

Any suggestions?
StuartR


snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: How to compare two versions of a workbook

Post by snb »

Importing the second workbook into the first one & Conditional formatting ?

or

Code: Select all

Sub M_snb()
  sn=workbooks(1).sheets(1).usedrange
  sp=workbooks(2).sheets(1).usedrange

  for j=1 to ubound(sn)
    for jj=1 to ubound(sn,2)
      if sp(j,jj)<>sn(j,jj) then workbooks(1).sheets(1).cells(j,jj).interior.color=vbred
    next
  next
End Sub
Last edited by snb on 17 Apr 2023, 13:41, edited 1 time in total.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: How to compare two versions of a workbook

Post by StuartR »

Thank you that was exactly the suggestion I needed.
I had to make some edits as workbooks(1) was my personal.xlsb, and I had moved the two worksheets to a single workbook. Also to define all variables as I use Option Explicit.

Here is the code that did the trick for me.

Code: Select all

Sub M_snb()
    Dim j As Integer
    Dim jj As Integer
    Dim sn As Variant
    Dim sp As Variant
    Dim Diffs As Integer
    
  sn = Workbooks(2).Sheets(1).UsedRange
  sp = Workbooks(2).Sheets(2).UsedRange

    For j = 1 To UBound(sn)
        For jj = 1 To UBound(sn, 2)
            If sp(j, jj) <> sn(j, jj) Then
                Diffs = Diffs + 1
                Workbooks(2).Sheets(1).Cells(j, jj).Interior.Color = vbRed
                Workbooks(2).Sheets(2).Cells(j, jj).Interior.Color = vbRed
            End If
        Next
    Next
    MsgBox "Found " & Diffs & " differences"
End Sub
StuartR


snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: How to compare two versions of a workbook

Post by snb »

If you can't resist the inclination to declare local variables you should consider:
- variant is the default variable type
- integer is restricted to 2 ^15 and a workbook has 2 ^20 rows.

So this declaration line suffices:

Code: Select all

Dim j As Long, jj As Long, Diffs as Long, sn, sp
But you won't notice any improvement in the functioning of the code.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: How to compare two versions of a workbook

Post by StuartR »

Thanks for the feedback. I like to define variables as it prevents me spelling them wrong later in the code
StuartR