Excel freezes when running macro

Question

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim i As Integer

    Set KeyCells = Range("H4:H100")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
        i = Range(Target.Address).Row
        If Cells(i, "L") = "" Then
            Cells(i, "H") = "In Progress"
        End If
    End If
End Sub

My excel keeps on freezing after running the script. Anyone can help me on this?

Solution 1

When using event handlers and making changes that could trigger the event handler again, it's good practice to disable event monitoring by declaring:

Application.EnableEvents = False

Be sure to re-enable events before your procedure ends otherwise they will remain disabled until you restart Excel.

Your code with events disabled and re-enabled:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim i As Integer

    Set KeyCells = Range("H4:H100")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
        i = Range(Target.Address).Row
        Application.EnableEvents = False
        If Cells(i, "L") = "" Then
            Cells(i, "H") = "In Progress"
        End If
        Application.EnableEvents = True
    End If
End Sub

Solution 2

The problem is simple. You check some range for a change. If it is changed, you perform some action, which changes a cells in checked range. This leads to a change event of this range and ... calls the method. And again and again...

To avoid this problem you must to create global or static flag and ignore all secondary calls:

Dim IsWorking As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If IsWorking Then Exit Sub         ' Exit if it is a 'slave' call
IsWorking = True                   ' Set 'main call is processed' flag
' perform actions you need
IsWorking = False                  ' Drop the flag, main call is finished
Exit Sub

Additionally: the Target can be a multicell range, not a single cell. You may to check the intersection for each row in it at least - maybe more than one cell is to be changed to "In Progress" value...

Additionally-2: Add Application.DoEvents statement(s) to the code - it allows to break the code execution by Ctrl-C if the macro 'freeses'.