Private Sub in endless loop

30136353

Board Regular
Joined
Aug 14, 2019
Messages
105
Hi Guys,

I have this private sub that gets stuck on an endless loop, error handling just shows the first line?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Change the cell colour and set the update flag to 1

Dim rngtemp1 As Range
Dim rngTemp3 As Range
Dim wsTemp As Worksheet
Dim lngLastRow As Long
Dim changedCell As Range

If ThisWorkbook.ActiveSheet.Name = "Network Data" Then


    Set wsTemp = ThisWorkbook.Worksheets("Network Data")
    
    lngLastRow = wsTemp.Range("D" & Rows.Count).End(xlUp).Row
    Set rngtemp1 = wsTemp.Range("h2:Z" & Trim(Str(lngLastRow)))
    
    If Not Intersect(Target, rngtemp1) Is Nothing Then
        With Target.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Set rngTemp3 = Intersect(Target.EntireRow, wsTemp.Range("AD:AD"))
        rngTemp3.Value = "1"
            End With
    End If
End If

   If Not Intersect(Range("V:V"), Target) Is Nothing Then


        For Each changedCell In Intersect(Range("V:V"), Target).Cells
   
            If changedCell.Offset(0, -3).Value = changedCell.Value Then
            changedCell.Offset(0, 1).Value = "Completed"
            End If
            
        Next changedCell
    End If

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You are changing cells on a sheet from inside its Change event, which will therefore get triggered recursively. Use:

Code:
Application.EnableEvents = False

to disable events while changing the cells, then

Code:
Application.EnableEvents = True

to reset before the code ends.
 
Upvote 0
You are changing cells on a sheet from inside its Change event, which will therefore get triggered recursively. Use:

Code:
Application.EnableEvents = False

to disable events while changing the cells, then

Code:
Application.EnableEvents = True

to reset before the code ends.
Thanks for the input. That's it working, much appreciated !
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top