Avoid dependant event trigger each other

Question

I have a Worksheet_change in which two events are checked (edits on cells of column C and edits on cells of column D). The problem is that an edit on column C's cells modify the value of column D's cells (and viceversa), so the Worksheet_change is triggered repeatedly and excel eventually crashes.

How can I avoid the problem but maintaining my functionality?

    Private Sub Worksheet_Change(ByVal Target As Range)



Dim rgFound As Range
Dim defVal As Range
Dim currParam As Range
Dim currParamDict As Range

Set targ = Intersect(Target, Range("A:A"))
If Not targ Is Nothing Then


With Worksheets("FT_CASE_xx")
    For Each defVal In .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Offset(, 1)

            Set currParam = defVal.Offset(, -1)

            Dim xlFirstChar As String
            xlFirstChar = Left$(currParam, 1)


            If xlFirstChar = "B" Then

                Set rgFound = Worksheets("DEF_BOOLEAN").Range("A:A").Find(currParam.value)
                defVal.Offset(, 1).Interior.Color = RGB(230, 230, 230)
                defVal.Offset(, 1).Locked = True

                defVal.Offset(, 2).Select

                With Selection.Validation
                                .Delete
                                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="TRUE,FALSE"
                                .IgnoreBlank = True
                                .InCellDropdown = True
                                .InputTitle = ""
                                .ErrorTitle = ""
                                .InputMessage = ""
                                .ErrorMessage = ""
                                .ShowInput = True
                                .ShowError = True
                End With
            Else
                Set rgFound = Worksheets("DEF_FLOAT").Range("A:A").Find(currParam.value)
                defVal.Offset(, 1).Interior.ColorIndex = 0
                defVal.Offset(, 1).Locked = False
                defVal.Offset(, 2).Locked = False

                defVal.Offset(, 1).NumberFormat = "0.000"
                defVal.Offset(, 2).NumberFormat = "0.000"
                defVal.Offset(, 3).NumberFormat = "0.000"
            End If

            If rgFound Is Nothing Then
                Debug.Print "Name was not found."
            Else

            If xlFirstChar = "B" Then
                Set currParamDict = rgFound.Offset(, 3)
            Else
               Set currParamDict = rgFound.Offset(, 5)
            End If

            defVal.value = currParamDict.value

            End If

    Next defVal
End With

Else
    Set targ = Intersect(Target, Range("C:C"))
    If Not targ Is Nothing Then

        Dim coeffVal As Range
        Dim currVal As Range
        Dim RequestedVal As Range

        With Worksheets("FT_CASE_xx")
            For Each coeffVal In .Range("C2", .Range("C" & Rows.Count).End(xlUp))

                    Set currVal = coeffVal.Offset(, -1)
                    Set RequestedVal = coeffVal.Offset(, 1)
                    Set ParamName = coeffVal.Offset(, -2)

                    Dim xlFirstChar2 As String
                    xlFirstChar2 = Left$(ParamName, 1)

                    If ((xlFirstChar2 = "F") And (IsEmpty(coeffVal.value) = False)) Then
                       RequestedVal.value = coeffVal.value * currVal.value
                    End If

            Next coeffVal
        End With

    Else

            Set targ = Intersect(Target, Range("D:D"))
            If Not targ Is Nothing Then

            Dim coeffsVal As Range
            Dim val As Range
            Dim reqVal As Range
            Dim Parameter As Range

            With Worksheets("FT_CASE_xx")
                For Each reqVal In .Range("D2", .Range("D" & Rows.Count).End(xlUp))

                        Set coeffsVal = reqVal.Offset(, -1)
                        Set val = reqVal.Offset(, -2)
                        Set Parameter = reqVal.Offset(, -3)

                        Dim xlFirstChar3 As String
                         xlFirstChar3 = Left$(Parameter, 1)

                        If ((xlFirstChar3 = "F") And (IsEmpty(reqVal.value) = False)) Then
                            If val.value = 0 Then
                                coeffsVal.value = reqVal.value
                            Else
                                coeffsVal.value = reqVal.value / val.value
                            End If
                        End If

              Next reqVal
            End With

    Else
                Exit Sub
    End If
    End If
End If

End Sub

Maybe a different management of target intersection? How?

Answer 1

My favoured method (which can also be useful in other situations) is to create a variable at global or module level (as required) then check this on each run of the code

Private disableEvents as Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If disableEvents Then Exit Sub
    disableEvents=True

    <code here>

    disableEvents=False
End sub