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

Hi Fullers,

If Target=Range("Term") then

Target is a Range object.

HTH
--
AP

"fullers" a écrit dans le message de
news: ...
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

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



"Ardus Petus" wrote in message
...
Hi Fullers,

Target=Range("Term") then

Target is a Range object.

HTH
--
AP

"fullers" a écrit dans le
message de news:
...
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

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)

"fullers" wrote in message
...
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