Identify defined name range by another cell value
 

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.

Identify defined name range by another cell value
 

Have you looked at the excel function INDIRECT?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Identify defined name range by another cell value
 

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

"Spencer Hutton" wrote:

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.

Identify defined name range by another cell value
 

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


"Spencer Hutton" wrote:

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.

Identify defined name range by another cell value
 

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" wrote:

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


"Spencer Hutton" wrote:

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.

Identify defined name range by another cell value
 

i changed it to:

Dim s As String

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

and it works.

"Sam Wilson" wrote:

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" wrote:

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


"Spencer Hutton" wrote:

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.

Identify defined name range by another cell value
 

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").V alue).Locked = True

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

Rick


"Spencer Hutton" wrote in message
...
i changed it to:

Dim s As String

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

and it works.

"Sam Wilson" wrote:

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" wrote:

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


"Spencer Hutton" wrote:

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.