Creating, Selecting and Formatting Named Ranges Via VBA

Creating Named Ranges

Creating a named range via VBA is much less involved. You can directly define the Name property of the Range object:

Sheets("Sheet3").Range("A1:D10").Name = "MyData"

Selecting Named Ranges

You simply pass the name of the range through the Range object. This allows you to select the range:

Range("MyData").Select

Formatting Named Ranges

As with normal ranges, you can refer to the range using the With…End With statement. This statement tells Excel that any action you perform applies to the object to which you’ve pointed. This not only prevents you from having to repeat syntax, but it also allows for the easy addition of actions by simply adding them between the With and End With statements.

With Range("MyData")
    .NumberFormat = "#,##0"
    .Font.Bold = True
    .Font.Color = RGB(255, 0, 255)
    .Interior.ColorIndex = 35
End With

See more about Formatting a Range of Cells In Excel VBA.

Full VBA Code

Sub NamedRanges()
    Sheets("Sheet3").Range("A1:D10").Name = "MyData"
    With Range("MyData")
        .NumberFormat = "#,##0"
        .Font.Bold = True
        .Font.Color = RGB(255, 0, 255)
        .Interior.ColorIndex = 35
    End With
End Sub

See the screenshot:

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert -> Module.
   
4. Type or paste the code in the newly created module.