Running Macro Crashes Excel

[cssfonseca]

I'm trying to run a macro but now it keeps freezing excel. It runs with 10 cells, but when the macro is applied to almost two hundred, it freezes and crashes.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub eancheck()

Dim s1 As Worksheet, s2 As Worksheet
Dim Msg AsString
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet3")
Dim lr1 AsLong, lr2 AsLong
lr1 = s1.Range("A"& Rows.Count).End(xlUp).Row
lr2 = s2.Range("a"& Rows.Count).End(xlUp).Row
Dim i AsLong, j AsLong
Application.ScreenUpdating =False
For i =2To lr1
s1.Cells(i,"D").Interior.ColorIndex =0
For j =2To lr2
If s2.Range("A"& j)= s1.Range("D"& i)Then
's1.Range("D" & i) = s2.Range("B" & j)
s1.Cells(i,"D").Interior.ColorIndex =3
EndIf
Next j
Next i
Application.ScreenUpdating =True

EndSub

</code>I'm having problems with other macros too, and I think is because of the size of the range. How can I fix it?
Note: The macro runs when searching 10 values in a sheet with two columns with almost 200.000 values each, but when instead of 10 is 200, crashes.

 

[vmjan02]

I think the problem is here
"Dim i AsLong, j AsLong"

i and j should be interger.

 

[BarryL]

are you trying to compare one list with another? it looks like using the VBA dictionary might be a better solution.

You need to reference Microsoft Scripting Runtime for the below to work. Ive copied the below from SO to show you how to do it.

Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">\Windows\System32\scrrun.dll</code>

• To reference this file, load the Visual Basic Editor (<kbd style="margin: 0px 0.1em; padding: 0.1em 0.6em; border: 1px solid rgb(173, 179, 185); font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: 1.4; font-family: Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 11px; vertical-align: baseline; box-sizing: inherit; display: inline-block; text-shadow: rgb(255, 255, 255) 0px 1px 0px; background-color: rgb(225, 227, 229); border-radius: 3px; box-shadow: rgba(12, 13, 14, 0.2) 0px 1px 0px, rgb(255, 255, 255) 0px 0px 0px 2px inset; white-space: nowrap;">ALT</kbd>+<kbd style="margin: 0px 0.1em; padding: 0.1em 0.6em; border: 1px solid rgb(173, 179, 185); font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: 1.4; font-family: Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 11px; vertical-align: baseline; box-sizing: inherit; display: inline-block; text-shadow: rgb(255, 255, 255) 0px 1px 0px; background-color: rgb(225, 227, 229); border-radius: 3px; box-shadow: rgba(12, 13, 14, 0.2) 0px 1px 0px, rgb(255, 255, 255) 0px 0px 0px 2px inset; white-space: nowrap;">F11</kbd>)
• Select Tools > References from the drop-down menu
• A listbox of available references will be displayed
• Tick the check-box next to '<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Microsoft Scripting Runtime</code>'
• The full name and path of the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">scrrun.dll</code> file will be displayed below the listbox
• Click on the <kbd style="margin: 0px 0.1em; padding: 0.1em 0.6em; border: 1px solid rgb(173, 179, 185); font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: 1.4; font-family: Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 11px; vertical-align: baseline; box-sizing: inherit; display: inline-block; text-shadow: rgb(255, 255, 255) 0px 1px 0px; background-color: rgb(225, 227, 229); border-radius: 3px; box-shadow: rgba(12, 13, 14, 0.2) 0px 1px 0px, rgb(255, 255, 255) 0px 0px 0px 2px inset; white-space: nowrap;">OK</kbd> button.

Sub test1()
Dim d As New Scripting.Dictionary, rng As Range, i As Long, a As Variant, s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet3")


a = s2.Range("A2:A" & s2.Range("a" & Rows.Count).End(xlUp).Row).Value


For i = LBound(a) To UBound(a)
d.Add a(i, 1), 1
Next


For Each rng In s1.Range("D2:D" & s1.Range("D" & Rows.Count).End(xlUp).Row)
If d.Exists(rng.Value) Then rng.Interior.ColorIndex = 3
Next
End Sub