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

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
>

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" <(E-Mail Removed)> skrev i meddelelsen
news:f400d76e-cd50-4eb3-88a3-(E-Mail Removed)...
> 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

thanks lot guys