Macro problem!!!!

[MarkReddell]

In the code below everything work great other than the bold type! I need to run the macro when I delete "A18"!!! Any suggestion???Thanx!!!!

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1:A4,A17,A18,A19,B2,C7:F7,G4")) Is Nothing And _
Target.Value <> "" Then

Application.EnableEvents = False

Select Case Target.Address(0, 0)

Case "A1"
If UCase(Target.Value) = "MAYBE" Then
CLEAR_TRADE_INFO
Else
SORT_LIST
End If

Case "A18"

If Not Intersect(Target, Range("A18")) Is Nothing Then
If IsEmpty(Target.Value) Then
COMPARE_DIFFERENT_TERMS
Else
COMPARE_SAME_TERMS
End If
End If
Case "A2", "A3", "A4", "C7", "D7", "E7", "F7", "A19"
SORT_LIST

Case "G4"
Select Case Target.Value
Case 1: ONE_SCENARIO
Case 2: SHOW_TWO_SCENARIOS
Case 3: SHOW_THREE_SCENARIOS
Case 4: SHOW_FOUR_SCENARIOS
End Select

Case "A17"
If UCase(Target.Value) = "SHOW_TERMS" Then
SHOW_TERMS_ONLY
ElseIf UCase(Target.Value) = "DONT_SHOW" Then
DONT_SHOW_TERMS_ONLY
End If

Case "B2"
Application.Run Target.Value

End Select

Application.EnableEvents = True
End If

End Sub

 

[jbeaucaire]

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target(1), Range("A1:A4,A17,A18,A19,B2,C7:F7,G4")) Is Nothing Then
    Application.EnableEvents = False
    
    Select Case Target(1).Address
        Case "$A$18"
            If IsEmpty(Target(1).Value) Then
                COMPARE_DIFFERENT_TERMS
            Else
                COMPARE_SAME_TERMS
            End If
            
        Case Else
            If Target(1) <> "" Then
            
                Select Case Target(1).Address
                    Case "$A$1"
                        If UCase(Target(1)) = "MAYBE" Then
                            CLEAR_TRADE_INFO
                        Else
                            SORT_LIST
                        End If
                    
                    Case "$G$4"
                        Select Case Target(1).Value
                            Case 1: ONE_SCENARIO
                            Case 2: SHOW_TWO_SCENARIOS
                            Case 3: SHOW_THREE_SCENARIOS
                            Case 4: SHOW_FOUR_SCENARIOS
                        End Select
    
                    Case "$A$17"
                        If UCase(Target(1)) = "SHOW_TERMS" Then
                            SHOW_TERMS_ONLY
                        ElseIf UCase(Target(1)) = "DONT_SHOW" Then
                            DONT_SHOW_TERMS_ONLY
                        End If
    
                    Case "$B$2"
                        Application.Run Target(1).Value
    
                    Case Else
                        SORT_LIST
                End Select
            End If
    End Select
    
    Application.EnableEvents = True
End If

End Sub

 

[MarkReddell]

Thanx very much!!! This is Awesome!!! It Works, It Works!!!! Have a Blessed Day & a Merry CHRISTmas!!!!!!