Simply change the Target cell and the range.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "D3:E22"
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$C$3" And Target.Value = "" Then
Me.Range(WS_RANGE).ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord

On Wed, 3 Mar 2010 06:43:01 -0800, Nicholas
wrote:

I have a similar issue. Help would be greatly appreciated.

I have a value in C3, and if it changes, I would like to have the contents
in D3:E22 cleared.

Thanks

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Const WS_RANGE As String = "E17:E22"
Set rng = Target.Offset(1, 1).Resize(1, 4)
On Error GoTo stoppit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
rng.ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord

On Sun, 30 Aug 2009 03:05:03 -0700, bigmaas
wrote:

Thank you for your reply. How would I expand this code to include the same
events for E17 thru E22? Many thanks
Regards
bigmaas

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "F16:I16"
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$E$16" And Target.Value = "" Then
Me.Range(WS_RANGE).ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

E16 is a manually input date. F16:H16 is manually input text. I16 is manually
input number. When I clear E16 manually, I want to have F16:I16 cleared
automatically.
Can anyone help? Thanks