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
550 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 ...
You can't do much in Excel VBA without knowing how to refer to and select cells! In this part of the lesson, you'll learn a few of the basic techniques for selecting single cells.
You can click here to download the file used for this page.
You can click here to download a file containing the sample 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 data we'll work with.
Now open the VBE and insert a new module into the project.
You can select a cell by its reference using the Range property. To demonstrate this, create a new subroutine and begin writing an instruction as shown below:
The tooltip indicates that you need to enter a reference to at least one cell.
In this example, we'll refer to cell which contains the name of the best film in the list:
Enter the cell reference within a set of double-quotes. Feel free to disagree with the choice of best film.
We can now apply a method to select the cell we've referenced:
Choose the Select method from the IntelliSense list or simply type it in.
Once a cell has been selected we can refer to it with the ActiveCell property. We can use this to highlight the selected cell in a different colour:
You can change the cell colour as shown. Type rgb and then press Ctrl + Space to show the list of colours.
You can run the subroutine to check that the correct cell is selected and coloured:
The end result should resemble the example shown here. Feel free to choose a different cell and fill colour!
You can also refer to a cell by writing its reference in a set of square brackets. This is an old-fashioned technique but is quicker to write. The example below would select cell B6:
[B6].Select
If you prefer to, you can select a cell using its row and column number by using the Cells property. Create a new subroutine and begin writing an instruction as shown below:
The tooltip indicates that you can enter a row and column number to reference a cell.
Next, enter the number of the row followed by the number of the column. In the example below we're referring to cell B3:
Enter the two numbers, separated by a comma.
You can then apply the Select method to the cell:
You won't see the IntelliSense list appear when referring to a single cell with the Cells property.
Once the cell has been selected, you can use the ActiveCell property to refer to it and change some of its properties:
Here we're changing the fill colour of the cell.
When you run the subroutine you'll see the specified cell's fill colour has changed:
This time we've highlighted the worst film in the list.
To practise selecting single cells using cell references:
Sub Copy_Best_Film()
End Sub
Sub Copy_Best_Film()
Range("A14").Select
ActiveCell.Value = "Best"
End Sub
Sub Copy_Best_Film()
Range("A14").Select
ActiveCell.Value = "Best"
Range("B6").Select
ActiveCell.Copy
End Sub
Sub Copy_Best_Film()
Range("A14").Select
ActiveCell.Value = "Best"
Range("B6").Select
ActiveCell.Copy
Range("B14").Select
ActiveCell.PasteSpecial
End Sub
The selected cell, along with all of its formatting, will be copied to B14.
To practise selecting cells using row and column numbers:
Sub Copy_Worst_Film()
End Sub
Sub Copy_Worst_Film()
Cells(15, 1).Select
ActiveCell.Value = "Worst"
End Sub
Sub Copy_Worst_Film()
Cells(15, 1).Select
ActiveCell.Value = "Worst"
Cells(3, 2).Select
ActiveCell.Copy
End Sub
Sub Copy_Worst_Film()
Cells(15, 1).Select
ActiveCell.Value = "Worst"
Cells(3, 2).Select
ActiveCell.Copy
Cells(15, 2).Select
ActiveCell.PasteSpecial
End Sub
The selected cell will be copied, along with its formatting, into the specified cell.
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.