kkmoslephour
New Member
- Joined
- May 8, 2014
- Messages
- 27
Hi guys this is my first post in this forum and I am new to macros, so please any feed back would help a lot 
I have a couple questions trying to program this, but let me ask them one at a time now. First, I am trying to do a file comparison macros on excel and highlight differences, however ran into a little error while doing this. In the code i have stated which line it is having the problem, I have read online articles and many people say that it is not good practice to use the '.select' so i am suspecting if that is why my error is occurring?

I have a couple questions trying to program this, but let me ask them one at a time now. First, I am trying to do a file comparison macros on excel and highlight differences, however ran into a little error while doing this. In the code i have stated which line it is having the problem, I have read online articles and many people say that it is not good practice to use the '.select' so i am suspecting if that is why my error is occurring?
Code:
Option Explicit
Sub Compare()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim diffB As Boolean
Dim r As Long, c As Integer, m As Integer, i As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Application.DisplayAlerts = True
Set wb1 = Workbooks.Open("C:\A319")
Set ws1 = wb1.Worksheets("BuildSheet")
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
Set wb2 = Workbooks.Open("C:\A320")
Set ws2 = wb2.Worksheets("BuildSheet")
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
For i = 2 To lr1
diffB = True
Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
For r = 2 To lr2
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(i, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 = cf2 Then
diffB = False
ws1.Cells(i, c).Interior.ColorIndex = 0
ws1.Cells(i, c).Select
Selection.Font.Bold = False
Exit For
End If
Next r
If diffB Then
DiffCount = DiffCount + 1
ws1.Cells(i, c).Interior.ColorIndex = 19
ws1.Cells(i, c).Select ----------------------------------------> error occurred here
Selection.Font.Bold = True
End If
Next i
Next c
Application.StatusBar = "Formatting the report..."
'Columns("A:IV").ColumnWidth = 10
m = DiffCount - 1
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox m & " cells contain different values!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub