Identify defined name range by another cell value

[Spencer Hutton]

I have a defined range "MyCity"
Cell C4 on sheet "INFO" has the text MyCity (this changes by code)
The range "MyCity" is on sheet "Index"

how do I in VBA refer to the range "MyCity" or whatever range's name is
shown in cell C4.
My goal is to change the formatting of whatever range is listed in cell C4.

 

[Bernard Liengme]

Have you looked at the excel function INDIRECT?
best wishes

 

[Sam Wilson]

range(worksheets("INFO").range("c4").value) will point you at the range you
want.

 

[Spencer Hutton]

I have this code and it does not work, i get an error. The range whose name
is in C4 is on another sheet, i am not sure if that matters.

Dim s As String

s = Sheets("INFO").Range("C4").Value
Range(s).Locked = True

 

[Sam Wilson]

What error do you get? I've just tried your code and it works on mine.

Do you have any spaces in the cell containing MyCity, ie is the actual value
"MyCity " rather than "MyCity"

 

[Spencer Hutton]

i changed it to:

Dim s As String

s = ActiveWorkbook.Worksheets("INFO").Range("C4").Value
ActiveWorkbook.Worksheets("Index").Range(s).Locked = True

and it works.

 

[Rick Rothstein \(MVP - VB\)]

I was just about to post this combined line in which the Range is embedded
with another Range for that...

Sheets("Index").Range(Sheets("Info").Range("C4").Value).Locked = True

As you can see, there is no need for the intermediate "s" variable in order
to carry the defined name forward.

Rick