Identify defined name range by another cell value

  • Thread starter Thread starter Spencer Hutton
  • Start date Start date
S

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.
 
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
 
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"
 
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.
 
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
 
Back
Top