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.

A	B	C	D	E	F	G	H	I	J	K	L
Type	Nbr	Post	Auto Rev <tbody> </tbody>	Nbr <tbody> </tbody>	Nbr <tbody> </tbody>	Date <tbody> </tbody>	Create User <tbody> </tbody>	Description <tbody> </tbody>	Balance <tbody> </tbody>	Amount <tbody> </tbody>	Amount <tbody> </tbody>
AP	AD	65465	65464	X	Y	4-26	XXX	sdfsdfs	asdfd	0 <tbody> </tbody>	43053 <tbody> </tbody>
AP	AD	65481	21548	X	Y	4-12	XXX	sdfsdf	adfas	0	54152
AP	VO	46481	54123	X	Y	5-3	XXX	asdfasd	adsfa	43053	0

<tbody>
</tbody>

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.