Target.Range

[azizrasul]

I have the following code

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$5" Then
        Call Generate_Newlist
    End If

End Sub

which I want to change to something like

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Range = Range("StartDate") Then
        Call Generate_Newlist
    End If

End Sub

as the value in C5 may change position to say D5 by a user but if I use the range name ("StartDate") then hopefully the code will still work!

When I use the latter code I get an error (argument not optional) on the .Range bit of

If Target.Range = Range("StartDate") Then

 

[Andrew Poulsom]

Try:

If Target.Address = Range("StartDate").Address Then

 

[azizrasul]

Great that worked. Thanks.

So when or how is Target.Range used???

 

[Ruddles]

I don't think it is. Where have you seen that?

Target is already a range, so I don't know what that would mean. It would be like saying Range("A1").Range.

 

[Andrew Poulsom]

Great that worked. Thanks.

So when or how is Target.Range used???

When applied to a Range object (which Target is) the Range property expects a reference as its argument, eg Range("A1"). You can't use it on its own like you can with eg the AutoFilter object. Another point is that you can't use = to test the equality of objects.

 

[azizrasul]

When you write Target., from the list that appears Range is an option. That is why I thought that Target.Range was valid.

 

[Andrew Poulsom]

Yes, Range is a property of the Range object, but it has a required Cell1 argument.

 

[Sektor]

Range("A1") is the first cell in Target. It's not real A1.

[COLOR="Blue"]If[/COLOR] Target.Range("A1") = Range("StartDate") [COLOR="Blue"]Then[/COLOR]
...

 

[azizrasul]

OK I understand. There was obviously a missing piece of the jigsaw for me.
Thanks all.

 

[Ruddles]

Thanks Andrew and Sektor - I didn't know that.

Now I just need to find somewhere I can use it...