Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
551 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
When you want to apply the same changes to many cells, it would be incredibly tedious to have to do it one cell at a time. Fortunately, you can select and modify multiple cells, as this page shows.
You can click here to download the file used for this page.
You can click here to download a file containing the finished code.
To get started, extract and open the file linked to in the Files Needed section above. You should see a basic table of information about films:
This is the basic table we'll work with.
For the first example, we'd like to format the column headings of the table. Start by opening the VBE, inserting a new module and creating a subroutine as shown below:
The starting point for our example.
Next, refer to the Range property and open a set of parentheses:
The tooltip seems to indicate that we can only reference two cells, although this is a little misleading.
You can enter the address of the first and last cell in the range you want to reference, separated by a colon:
Enter the cell references in a set of double quotes.
You can then close the parentheses and apply the Select method to the range:
Apply the Select method to the range.
After selecting a single cell you can use the ActiveCell property to reference it. When you have multiple cells selected however, this isn't much use:
The ActiveCell property only returns a reference to a single cell, not the entire range that is selected. Here the active cell is A1.
To refer to the entire range of selected cells, you can use the Selection property:
The Selection property returns a reference to the entire range of selected cells.
You can apply methods and properties to the range returned by Selection, although you sadly won't receive any help from the IntelliSense list:
Here we're changing the fill colour of the selected cells.
Run the subroutine to check that it works:
You should see something resembling this.
You can select multiple cells which aren't next to each other in a worksheet. For the next example we'd like to highlight the details of the two Avengers films in the list:
We want to select cells A6:D6 and A8:D8 at the same time.
Begin a new subroutine and refer to the Range property:
We can pass as many cell references as we like into the Cell1 parameter.
Open a set of double-quotes and write the address of the first block of cells:
Enter the cell references of the first block of cells.
Now type a comma followed by the next cell reference:
Enter the address of the second block of cells.
You can continue listing cell references in this way, separated by commas. When you've finished listing cells, close the double-quotes and parentheses:
You can apply the Select method to the list of ranges that you've referenced.
You can use the Selection property again to refer to all of the selected cells:
You can modify some formatting properties of the selected cells.
Run the subroutine and check the results:
You should see these cells highlighted.
To practise selecting a block of cells:
Sub Format_Gross_Values()
End Sub
Sub Format_Gross_Values()
Range("C2:C11").Select
End Sub
Sub Format_Gross_Values()
Range("C2:C11").Select
Selection.NumberFormat = "\$#,##0"
End Sub
The numbers in column C should be formatted as shown here.
To practise selecting non-adjacent cells:
Sub Format_Star_Wars()
End Sub
Sub Format_Star_Wars()
Range("A4:D4, A10:D10").Select
End Sub
Sub Format_Star_Wars()
Range("A4:D4, A10:D10").Select
Selection.Interior.Color = rgbBlack
Selection.Font.Color = rgbYellow
End Sub
You should see rows 4 and 10 formatted as shown.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.