Matching loop causing "not respond" to excel program

[Kabasauls]

Hello,

Situation: Accounting database that needs to be matched based on the last two columns. Any unmatched rows are manually fixed.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]Type[/TD]
[TD="align: center"]Nbr[/TD]
[TD="align: center"]Post[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Auto Rev[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Nbr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Nbr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Create User[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Balance[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]AP[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]65465[/TD]
[TD="align: center"]65464[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]4-26[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]sdfsdfs[/TD]
[TD="align: center"]asdfd[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="width: 54, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="width: 83, align: center"]43053[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]AP[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]65481[/TD]
[TD="align: center"]21548[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]4-12[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]sdfsdf[/TD]
[TD="align: center"]adfas[/TD]
[TD="align: center"] 0[/TD]
[TD="align: center"] 54152[/TD]
[/TR]
[TR]
[TD="align: center"]AP[/TD]
[TD="align: center"]VO[/TD]
[TD="align: center"]46481[/TD]
[TD="align: center"]54123[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]5-3[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]asdfasd[/TD]
[TD="align: center"]adsfa[/TD]
[TD="align: center"]43053[/TD]
[TD="align: center"] 0[/TD]
[/TR]
</tbody>[/TABLE]

Logic: When the non-zero amount on column K matches the one in column L, the macro colors the respective rows. The macro skips the already colored cells and only compares the non-zero values. Once the macro attempts to match all the values, a filter is used to show only the non-color cells. These cells are the unmatched ones.

Issue: The macro worked with the original data of 188 rows. Once a bigger database was attempted, about 15000 rows, excel does not respond after a few seconds and crashes. The first time, there was a message that pointed out to this line of my code in my Scan sub:

If (Cells(i, 11).Interior.ColorIndex = xlNone) And (Cells(m, 12).Interior.ColorIndex = xlNone) Then

Macro:

Private Sub CommandButton1_Click()


screenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

'turn off some Excel functionality for efficiency
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting


Call clear_formats
Call Scan
Call filter


'Resets settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting


End Sub


Private Sub Scan()Dim Rows1 As Integer

Dim Rows2 As Integer
Dim lastRow As Integer
Dim i As Integer
Dim m As Integer
i = 1
m = 1
 
'Find number of rows with data
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
'Scan first column
For i = 2 To lastRow
    For m = 2 To lastRow
    If Cells(i, 11).Value <> 0 And Cells(m, 12).Value <> 0 Then
        'Check for a color
            If (Cells(i, 11).Interior.ColorIndex = xlNone) And (Cells(m, 12).Interior.ColorIndex = xlNone) Then
            'looks for a match
                If Cells(i, 11).Value = Cells(m, 12).Value Then
                    Rows(i).Interior.ColorIndex = 42
                    Rows(m).Interior.ColorIndex = 42
                End If
            End If
    End If
    Next m
Next i

End Sub

Private Sub filter()

'filter by colorindex = 42 in column 11
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$N$100288").AutoFilter Field:=11, Operator:= _
        xlFilterNoFill


End Sub


Private Sub clear_formats()
'Reset filter
Worksheets("Test").AutoFilterMode = False


'Reset formats of whole page


Cells.Select
Selection.ClearFormats




Range("A1").Select


End Sub


Private Sub CommandButton2_Click()


Application.ScreenUpdating = False


Call clear_formats


Application.ScreenUpdating = True


End Sub

Question: Why does the macro work on a smaller scale but crashes when I use about 15000 rows? Can it be the loop is too slow and process intensive?

 

[Kabasauls]

Ignore this post. I already figured it out. I do not know how to erase this from the forum.