Comparing two files, exclude header from selection

supritha - Updated on May 15, 2017 at 11:28 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - May 9, 2017 at 11:46 AM

please help with the comparison of two excel sheets using macro. The one I used is -

Sub All_Diffs_Highlighted()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Cell As Range
Dim sBook As String
If Workbooks.Count < 2 Then
MsgBox "Error: Only one Workbook is open" & vbCr & _
"Open a 2nd Workbook and run this macro again."
Exit Sub
End If
Set wb1 = ThisWorkbook
For Each wb2 In Workbooks
If wb2.Name <> wb1.Name Then Exit For
On Error Resume Next
Application.DisplayAlerts = False
sBook = Application.InputBox(Prompt:= _
"Compare this workbook (" & wb1.Name & _
") to...?", _
Title:="Compare to what workbook?", _
Default:=wb2.Name, _
If sBook = "False" Then Exit Sub
If Workbooks(sBook) Is Nothing Then
MsgBox "Workbook: " & sBook & " is not open."
GoTo ReDo1
Set wb2 = Workbooks(sBook)
End If
Application.ScreenUpdating = False
For Each ws1 In wb1.Sheets
If Not wb2.Sheets(ws1.Name) Is Nothing Then
Set ws2 = wb2.Sheets(ws1.Name)
For Each Cell In ws1.UsedRange
If Cell.Formula <> ws2.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws2.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
If ws1.UsedRange.Rows.Count <> _
ws2.UsedRange.Rows.Count Or _
ws1.UsedRange.Columns.Count <> _
ws2.UsedRange.Columns.Count Then
For Each Cell In ws2.UsedRange
If Cell.Formula <> ws1.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws1.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
End If
End If
Next ws1
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub Clear_Highlights_this_Sheet()
ActiveSheet.UsedRange. _
Interior.ColorIndex = xlNone
End Sub

Sub Clear_Highlights_All_Sheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.UsedRange.Interior.ColorIndex = xlNone
End Sub

this just finds the difference and highlights it when u run it once. when I update the data, I have to clear the highlight and run it again - also the headings gets highlighted if there is a difference :/. I want the hightlights to be done as and when the data is being updated and only for the data not the headings. how do I use a button to carry out the highlighting and resetting? hope you understand my question. Im still learning my basics.
please help me!

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Updated on May 9, 2017 at 11:46 AM
Hi Supritha,

Q1: Highlight data without headers.
You use 'ws1.UsedRange', which uses the entire used range including headers.
This will remove the header in the used range:

Dim MyRange As Range
Set MyRange = ws1.UsedRange
MyRange.Offset(1, 0).Resize(MyRange.Rows.Count - 1, MyRange.Columns.Count).Select

Q2: Clearing highlights when code is run.
You already have the code for this. Activate the code by using 'Call MacroNameHere'
Once you have determined wb1 and wb2, you can use
call Clear_Highlights_All_Sheets
call Clear_Highlights_All_Sheets

Q3: Using button to activate code.
Goto the Developers Ribbon, click on Insert and choose the ActiveX Commandbutton. Draw the button, right-click on the button and choose Show Code. Between the 2 provided code lines insert your code or better yet, use the Call method as shown in Q2. To use the button, click on Design Mode on the Developers Ribbon (found next to Insert).

Let us know if this helps you out and/or if you need further assistance.

Best regards,

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.