So Itried:

Dim Rangename As Range
Set Rangename = Range(Range(rangeDescription)) '' remember this is
INDIRECTION!

With source
.Interior.Color = vbRed
.Font.Size = 18
End With

Where RangeName and RangeDescription are variables

But Dim Rangename As Range therew an error message that says "duplicate
Declaration in scope."

I suppose I could get around this by just creatig range names and then
quoting them explicitly, but it would be nice If I could tie the name to the
column heading somehow so I don't have meaningless names that I have to
remember the real meaning of.....


What is the true meaning of Columnheading, Master?
Snatch the pebble from my hand.








"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???