Access Excel worksheet cells using a1 notation

A common task when using a macro in Microsoft Excel is to access Excel worksheet cells using a1 notation and then do something with it, such as enter a formula or change the format.
You can usually do this in one statement that identifies the range and also changes a property or applies a method.

A Range object in Visual Basic can be either a single Excel cell (Eg. A1) or a range of Excel cells (Eg. A1:D5).

The following example shows how to reference cells and ranges using A1 notation in Microsoft Excel.

You can refer to a cell or range of cells in the A1 reference style by using the Range property in Excel. The following subroutine changes the format of Excel cells A1:D5 to bold.

Macro code for reference:

Sub FormatRange()

Workbooks("Book1").Sheets("Sheet1").Range("A1:D5").Font.Bold = True

End Sub

Implementing the macro:

1. Click on Tools in the menu bar and select Macro. From the drop-down options select Visual Basic Editor
2. In the VBA editor window click on the insert menu in the menu bar and select Module
3. Type the above code. You can type the following code also and it will work although the above code is comprehensive:
   

   Sub FormatRange()

   Sheet1.Range("A1:D5").Font.Bold = True

   End Sub

4. Instead of bold you can use underline, italics, etc.
   

Watch the video below to see the macro in action: