Worksheet_Change function keeps crashing...why??

[biznez]

Hi, i have this code below and every time i make a change to the worksheet, Excel crashes. Whats the problem?

Please Login or Register  to view this content.

[nimrod]

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Lastrow = Worksheets("Edit").Cells(Worksheets("Edit").Rows.Count, "A").End(xlUp).Row
SEQ = 1
For I = 2 To Lastrow
If Cells(I, 6).Value = "GOOD" Or Cells(I, 6).Value = "BAD" Then
Cells(I, 7).Value = SEQ
SEQ = SEQ + 1
Else: Cells(I, 7).Value = ""
End If

Next I

Application.EnableEvents = True
End Sub

EXPLAINATION:
each of your changes in the loop triggered the Worksheet_change Event ... you need to turn this off before changing fields and then turn on again after e.g. Application.EnableEvents

[TMS]

A) because you are making changes to the worksheet within the Worksheet Change event handler, B) because you are not restricting the cells you monitor and C) you're looping through all the used cells in a column and changing them.

Use Application.EnableEvents = False before changes and True afterwards.

[biznez]

private sub worksheet_change(byval target as range)

application.enableevents = false
lastrow = worksheets("edit").cells(worksheets("edit").rows.count, "a").end(xlup).row
seq = 1
for i = 2 to lastrow
if cells(i, 6).value = "good" or cells(i, 6).value = "bad" then
cells(i, 7).value = seq
seq = seq + 1
else: Cells(i, 7).value = ""
end if

next i

application.enableevents = true
end sub

explaination:
Each of your changes in the loop triggered the worksheet_change event ... You need to turn this off before changing fields and then turn on again after

beauty !! Thank you !

[biznez]

private sub worksheet_change(byval target as range)

application.enableevents = false
lastrow = worksheets("edit").cells(worksheets("edit").rows.count, "a").end(xlup).row
seq = 1
for i = 2 to lastrow
if cells(i, 6).value = "good" or cells(i, 6).value = "bad" then
cells(i, 7).value = seq
seq = seq + 1
else: Cells(i, 7).value = ""
end if

next i

application.enableevents = true
end sub

explaination:
Each of your changes in the loop triggered the worksheet_change event ... You need to turn this off before changing fields and then turn on again after

beauty !! Thank you !