sulavsingh
04-15-2009, 11:15 AM
I cant get the > if statement portion to work. Please let me know what I am doing wrong.
Private Sub worksheet_Change(ByVal Target As Range)
'
' What_If Macro
'This Macros deletes one cell if another has a value in it
'For cell inputs (Medium Duty, Severe Service, and Heavy Duty)
Dim Medium As Variant
Dim Severe As Variant
Dim Heavy As Variant
Medium = Cells(14, 11)
Severe = Cells(38, 11)
Heavy = Cells(58, 11)
Application.EnableEvents = False
If IsNumeric(Target.Value) Then
'Clear the Invalid entries
Range("L4").ClearContents
Range("L5").ClearContents
Range("L28").ClearContents
Range("L29").ClearContents
Range("L48").ClearContents
Range("L49").ClearContents
If Target.Address = "$K$4" Then
Range("K5").ClearContents
ElseIf Target.Address = "$K$5" Then
Range("K4").ClearContents
ElseIf Target.Address = "$K$28" Then
Range("K29").ClearContents
ElseIf Target.Address = "$K$29" Then
Range("K28").ClearContents
ElseIf Target.Address = "$K$48" Then
Range("K49").ClearContents
ElseIf Target.Address = "$K$49" Then
Range("K48").ClearContents
ElseIf Target.Value > Cells(14, 11) Then
Range(Target.Address).ClearContents
ElseIf Target.Value > Severe Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
ElseIf Range(Target.Address).Value > Heavy Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
End If
Else
MsgBox ("Invalid Input: ") & Target.Value
Range(Target.Address).ClearContents
Target.Next = "Invalid Input"
End If
Application.EnableEvents = True
End Sub
thanks for your help
Private Sub worksheet_Change(ByVal Target As Range)
'
' What_If Macro
'This Macros deletes one cell if another has a value in it
'For cell inputs (Medium Duty, Severe Service, and Heavy Duty)
Dim Medium As Variant
Dim Severe As Variant
Dim Heavy As Variant
Medium = Cells(14, 11)
Severe = Cells(38, 11)
Heavy = Cells(58, 11)
Application.EnableEvents = False
If IsNumeric(Target.Value) Then
'Clear the Invalid entries
Range("L4").ClearContents
Range("L5").ClearContents
Range("L28").ClearContents
Range("L29").ClearContents
Range("L48").ClearContents
Range("L49").ClearContents
If Target.Address = "$K$4" Then
Range("K5").ClearContents
ElseIf Target.Address = "$K$5" Then
Range("K4").ClearContents
ElseIf Target.Address = "$K$28" Then
Range("K29").ClearContents
ElseIf Target.Address = "$K$29" Then
Range("K28").ClearContents
ElseIf Target.Address = "$K$48" Then
Range("K49").ClearContents
ElseIf Target.Address = "$K$49" Then
Range("K48").ClearContents
ElseIf Target.Value > Cells(14, 11) Then
Range(Target.Address).ClearContents
ElseIf Target.Value > Severe Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
ElseIf Range(Target.Address).Value > Heavy Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
End If
Else
MsgBox ("Invalid Input: ") & Target.Value
Range(Target.Address).ClearContents
Target.Next = "Invalid Input"
End If
Application.EnableEvents = True
End Sub
thanks for your help