Comparing two files, exclude header from selection [Closed]

Report
-
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
-
Hello,

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
Next
'
On Error Resume Next
ReDo1:
Application.DisplayAlerts = False
sBook = Application.InputBox(Prompt:= _
"Compare this workbook (" & wb1.Name & _
") to...?", _
Title:="Compare to what workbook?", _
Default:=wb2.Name, _
Type:=2)
If sBook = "False" Then Exit Sub
If Workbooks(sBook) Is Nothing Then
MsgBox "Workbook: " & sBook & " is not open."
GoTo ReDo1
Else
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
Next
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!
Regards,
Supritha.

1 reply

Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
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
wb1.activate
call Clear_Highlights_All_Sheets
wb2.activate
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,
Trowa

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!