I found some really cool code that compares the two ranges more efficiently and turns red any cells interior colors that are different on the Main worksheet. Attached if interested - module password is still dmu123xls. The Compare button runs faster now than when i was using a zillion For Next Loops. I'm still figuring out how to show only the red rows or cells on the Summary worksheet. Presently, the Summarize button just copies the Main worksheet. Anyway, the really cool code, I think, is this.
Dim name1 As String
Dim name2 As String
Dim range1 As Range
Dim range2 As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean
Lastrow2 = ThisWorkbook.Worksheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
name1 = "A3:AK"
If Len(name1) = 0 Then Exit Sub
Set range1 = ThisWorkbook.Worksheets("Main").Range(name1 & Lastrow2)
name2 = "AM3:BW"
If Len(name2) = 0 Then Exit Sub
Set range2 = ThisWorkbook.Worksheets("Main").Range(name2 & Lastrow2)
' Make normal collections holding the cells.
Set cells1 = New Collection
For Each cell1 In range1.Cells
key = cell1.Row - range1.Row & "," & cell1.Column - _
range1.Column
cells1.Add cell1, key
Next cell1
Set cells2 = New Collection
For Each cell2 In range2.Cells
key = cell2.Row - range2.Row & "," & cell2.Column - _
range2.Column
cells2.Add cell2, key
Next cell2
' Examine the cells in the first collection.
For Each cell1 In cells1
On Error Resume Next
Err.Clear
key = cell1.Row - range1.Row & "," & cell1.Column - _
range1.Column
Set cell2 = cells2(key)
If Err.Number <> 0 Then
' The second cell is missing.
no_match = True
ElseIf cell1.Text <> cell2.Text Then
' The cells don't match.
no_match = True
Else
no_match = False
End If
' If the cells don't match, color cell1.
If no_match Then
With cell1.Interior
.Color = RGB(255, 204, 204)
.Pattern = xlSolid
End With
Else
With cell1.Interior
.ColorIndex = xlNone
End With
End If
Next cell1
' Examine the cells in the second collection.
For Each cell2 In cells2
On Error Resume Next
Err.Clear
key = cell2.Row - range2.Row & "," & cell2.Column - _
range2.Column
Set cell1 = cells1(key)
If Err.Number <> 0 Then
' The second cell is missing.
no_match = True
ElseIf cell2.Text <> cell1.Text Then
' The cells don't match.
no_match = True
Else
no_match = False
End If
' If the cells don't match, color cell2.
If no_match Then
With cell2.Interior
.Color = RGB(255, 204, 204)
.Pattern = xlSolid
End With
Else
With cell2.Interior
.ColorIndex = xlNone
End With
End If
Next cell2
I also changed it to import a before and after text file instead of html files. All this is attached in a zip file if anyone is interested or can apply the code to their needs.
Bookmarks