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?
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
This question generated 24 answers. To proceed to the answers, click here.
This thread is current as of May 08, 2014.