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?
How to compare two versions of a workbook
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
-
- 4StarLounger
- Posts: 548
- Joined: 14 Nov 2012, 16:06
Re: How to compare two versions of a workbook
Importing the second workbook into the first one & Conditional formatting ?
or
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.
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: How to compare two versions of a workbook
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.
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
-
- 4StarLounger
- Posts: 548
- Joined: 14 Nov 2012, 16:06
Re: How to compare two versions of a workbook
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:
But you won't notice any improvement in the functioning of the code.
- 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
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: How to compare two versions of a workbook
Thanks for the feedback. I like to define variables as it prevents me spelling them wrong later in the code
StuartR