Using range names with Worksheet_Change

[ElvisSteel]

Hi,

I need to run some code whenever a user changes the value in certain cells.
Currently the code is...

If Not Intersect(Target, Range("C4")) Is Nothing Then
  Do Something
End If

or sometimes...

 If ActiveCell.Address = "$C$4" Then
  Do Something
End If

which work fine, but I want to refer to a defined name rather than C4, so that if the address changes due to inserting/deleting rows or columns I don't have to change the code.
Is this possible?
I have tried

If Not Intersect(Target, Range("RangeName")) Is Nothing Then
  Do Something
End If

Have I missed something obvious?

Thanks

 

[Jon von der Heyden]

Hi

How is the name defined?

Perhaps;

If Not Intersect(Target, [COLOR=red]Sheets("Sheet1").[/COLOR]Range("RangeName")) Is Nothing Then

 

[ElvisSteel]

Jon,

Thanks for such a prompt response.

The original error I am getting is:
Method 'Range' of object '_worksheet' failed

After making your suggested change to include the sheet name I now get:
Application-defined or object-defined error

The range name in question is scoped to the workbook.

Thanks

 

[Jon von der Heyden]

There is bound to be an easier way but all I could think of is:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_SelectionChange([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]Dim[/COLOR] strRngAddress [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    strRngAddress = ActiveWorkbook.Names("test").RefersTo
    strRngAddress = [COLOR="Blue"]Mid[/COLOR](strRngAddress, 3, [COLOR="Blue"]Len[/COLOR](strRngAddress) - 3)
    
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Application.Intersect(Target, Range(strRngAddress)) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        [COLOR="Blue"]MsgBox[/COLOR] "beep"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[ElvisSteel]

Jon,

Thanks very much, your efforts are much appreciated