worksheet change event

[Guest]

I have the following worksheet change event that will be activated when there
is a change to cell E5.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Row = 1 Then
Call Macro1
End If
End If
End Sub

I have named cell E5 as "Term". What I would like to do is replace the 2 if
statements above with 1 that reads:

if Target.Range = Range("Term") then ...

However, this does not work and I get an error message that reads "Argument
not optional".

Any ideas on how I can get this to work?

Thanks in advance

 

[Ardus Petus]

Hi Fullers,

If Target=Range("Term") then

Target is a Range object.

HTH

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target,Range("Term")) Is Nothing Then
Call Macro1
End If
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Chip Pearson]

Target=Range("Term") then

This won't work. Both Target and Range("Term") are ranges, and
their default propreties are Value. This you are comparing the
value of Target and the value of Range("Term"). The code behaves
as if it were written as

Target.Value=Range("Term").Value then

It does not test whether Target is the same range as "Term".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com