Referring to Named Ranges [Excel 2003 VBA Language Reference]
Ranges are easier to identify by name than by A1 notation. To name a selected range, click the name box at the left end of the formula bar, type a name, and then press ENTER.
Referring to a Named Range
The following example refers to the range named "MyRange" in the workbook named "MyBook.xls."
Sub FormatRange() Range("MyBook.xls!MyRange").Font.Italic = True End Sub
The following example refers to the worksheet-specific range named "Sheet1!Sales" in the workbook named "Report.xls."
Sub FormatSales() Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin End Sub
To select a named range, use the GoTo method, which activates the workbook and the worksheet and then selects the range.
Sub ClearRange() Application.Goto Reference:="MyBook.xls!MyRange" Selection.ClearContents End Sub
The following example shows how the same procedure would be written for the active workbook.
Sub ClearRange() Application.Goto Reference:="MyRange" Selection.ClearContents End Sub
Looping Through Cells in a Named Range
The following example loops through each cell in a named range by using a For Each...Next loop. If the value of any cell in the range exceeds the value of
limit, the cell color is changed to yellow.
Sub ApplyColor() Const Limit As Integer = 25 For Each c In Range("MyRange") If c.Value > Limit Then c.Interior.ColorIndex = 27 End If Next c End Sub