I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

Right click sheet tabview codecopy/paste this
Private Sub Worksheet_Change(ByVal Target As Range)
'Every time the User inputs data in cell L36
'Then cells E40 thru E43 are cleared.
If Target.Address < Range("L36").Address Then Exit Sub
Range("e40:e43").ClearContents
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnL" wrote in message
...
I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

Paste this in your Sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("L36") Then
If Target < "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub

"JohnL" wrote:

I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

If you copy multiple cell an paste them over cell L36 you need to use a loop
like the code below:

Sub worksheet_change(ByVal target As Range)

For Each cell In target
If Not Intersect(cell, Range("L36")) Is Nothing Then
Range("E40:E43").ClearContents

End If
Next cell
End Sub


"JLGWhiz" wrote:

Paste this in your Sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("L36") Then
If Target < "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub

"JohnL" wrote:

I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

This is the correct way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("L36"), Target) Is Nothing Then
If Target < "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub


"JohnL" wrote:

I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

Don, This worked perfectly! I don't mean to look a gift horse in the mouth,
but if there is an If...Then statement why isn't there an End If?
Thanks again Don.

John

"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this
Private Sub Worksheet_Change(ByVal Target As Range)
'Every time the User inputs data in cell L36
'Then cells E40 thru E43 are cleared.
If Target.Address < Range("L36").Address Then Exit Sub
Range("e40:e43").ClearContents
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnL" wrote in message
...
I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

If its on ONE line then no end if needed.OR, if you use a continuation
character at the end of the line (space and underscore)

If Target.Address < Range("L36").Address _
Then Exit Sub
'no end if

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnL" wrote in message
...
Don, This worked perfectly! I don't mean to look a gift horse in the
mouth,
but if there is an If...Then statement why isn't there an End If?
Thanks again Don.

John

"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this
Private Sub Worksheet_Change(ByVal Target As Range)
'Every time the User inputs data in cell L36
'Then cells E40 thru E43 are cleared.
If Target.Address < Range("L36").Address Then Exit Sub
Range("e40:e43").ClearContents
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnL" wrote in message
...
I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

Hi John,

The reason JL says this is the correct way is because it is the more general
solution. Consider what you would need to write if the range where users
could enter data which triggered your macro was A1:A1000, using the first
method you would need to do 1000 IF's, so hard! In JL's case you would change
the Range("L36") to read Range("A1:A1000"), so simple!

In both cases you can use the sortcut notation [L36] or [A1:A1000] in which
case there is no need for the quotes or the Range(). It's faster to enter,
but the auto complete feature doesn't work with it and it may not be as
flexible in some situations.

--
Thanks,
Shane Devenshire


"JLGWhiz" wrote:

This is the correct way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("L36"), Target) Is Nothing Then
If Target < "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub


"JohnL" wrote:

I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John

Thank you Shane for the explanation. It is responses like yours which make
this not only a discussion group but also a learning group. Thanks again.
John

"ShaneDevenshire" wrote:

Hi John,

The reason JL says this is the correct way is because it is the more general
solution. Consider what you would need to write if the range where users
could enter data which triggered your macro was A1:A1000, using the first
method you would need to do 1000 IF's, so hard! In JL's case you would change
the Range("L36") to read Range("A1:A1000"), so simple!

In both cases you can use the sortcut notation [L36] or [A1:A1000] in which
case there is no need for the quotes or the Range(). It's faster to enter,
but the auto complete feature doesn't work with it and it may not be as
flexible in some situations.

--
Thanks,
Shane Devenshire


"JLGWhiz" wrote:

This is the correct way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("L36"), Target) Is Nothing Then
If Target < "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub


"JohnL" wrote:

I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John