Clear Contents of Offset cells automatically

[K]

Hi all, I got macro below which automatically put figures in Offset
cells when I put any figure in Target cell. It works fine but I want
that when I Clear Contents of Target cell by pressing Delete button on
Keyboard then Contents of Offset cells should also be cleared
automatically. Please can any friend can help

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
Target.Offset(, 1) = Target * 52
Target.Offset(, 2) = Target * 12
Else
End If
Whoops:
Application.EnableEvents = True
End Sub

 

[Mike H]

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
If Len(Target.Value) > 0 Then
Target.Offset(, 1) = Target * 52
Target.Offset(, 2) = Target * 12
Else
Target.Offset(, 1).ClearContents
Target.Offset(, 2).ClearContents
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

 

[Per Jessen]

Hi

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(, 1) = Target * 52
Target.Offset(, 2) = Target * 12
Else
Target.Offset(, 1) = ""
Target.Offset(, 2) = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Regards,
Per

 

[K]

thanks lot guys