Clear Contents of Offset cells automatically
 

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

Clear Contents of Offset cells automatically
 

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

"K" wrote:

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

Clear Contents of Offset cells automatically
 

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" skrev i meddelelsen
...
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

Clear Contents of Offset cells automatically
 

thanks lot guys