Worksheet_Change Breaking

[vahnx]

        Cells(Target.Row, 50).Formula = "=AVERAGE(N" & Target.Row & ":AW" & Target.Row & avgstr & ")"
        If IsError(Cells(Target.Row, 50)) Then
           Cells(Target.Row, 50).Formula = ""
        End If

If I say delete value, Cell column 50 gets cleared but then it breaks Worksheet_Change, rendering all code inside useless until next excel launch. How could I go about fixing this?

 

[Jerry Sullivan]

If you post the entire procedure, it will be easier to help.

One possibility is that you are setting Application.EnableEvents = False during your code
and it is not getting reset to True prior to exiting.

 

[mikerickson]

The formula string is un-clear. What is avgStr and what is the formula supposed to be?
I would change the formula to something like

Cells(Target.Row, 50).FormulaR1C1 = "=AVERAGE(RC14:RC49)"

But what is avgstr?
What is the formula supposed to calculate?

 

[vahnx]

' ****************************************************************
' *** WHEN A VALUE IS CHANGED, THE FOLLOWING CODE IS TRIGGERED ***
' ****************************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim bgcol As Integer
    Dim found As Integer
    found = 0
    Dim avgstr As String
    avgstr = ""

     For x = 14 To 49
         If Cells(Target.Row, x) = "<0.005" Then
             found = found + 1
         End If
     Next x
     If found > 0 Then
         For x = 1 To found
             avgstr = avgstr & ",.0025"
         Next x
     End If
        
     If Cells(Target.Row, 1).Interior.ColorIndex = -4142 Then
         Cells(Target.Row, 50).Formula = "=AVERAGE(N" & Target.Row & ":AW" & Target.Row & avgstr & ")"
         If IsError(Cells(Target.Row, 50)) Then
             Cells(Target.Row, 50).Formula = ""    ' PROGRAM BOMBS
         Else
             If Cells(Target.Row, 50) >= 1 Then                          ' If final grade is big
                 Cells(Target.Row, 50).Font.Bold = True                  ' Bolden it
             Else                                                        ' Under 1
                 Cells(Target.Row, 50).Font.Bold = False                 ' No boldness
             End If
         End If
         Cells(Target.Row, 50).NumberFormat = "General"
     End If

End Sub