Access Excel worksheet cells using a1 notation

We have learnt how to create a new workbook using VBA. Next we learnt how to open the newly created workbook automatically using the 'Open' method. Today we learn how to access Excel worksheet cells so that we can enter data into an Excel worksheet using VBA and also perform manipulations like formatting on the entered data.

Now 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:

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()
   Range("A1").Value = "First Name"
   Range("B1").Value = "Last Name"
   Range("C1").Value = "Designation"
   Range("D1").Value = "Salary"
   Range("E1").Value = "Perks"
   Range("F1").Value = "Total Package"
   Sheets("Sheet1").Range("A1:F1").Font.Bold = True
   End Sub
4. Instead of bold you can use underline, italics, etc.
   

Watch the Excel training video below to see the macro in action: