Macro 31: Selecting and Formatting a Range
One of the basic things you need to do in VBA is to select a specific range to do something with it. This
simple macro selects the range D5:D16.
How it works
In this macro, you explicitly define the range to select by using the Range object.
Sub Macro31a()
Range(“D5:D16”).Select
End Sub
After the range of cells is selected, you can use any of the Range properties to manipulate the cells.
We’ve altered this macro so that the range is colored yellow, converted to number formatting, and
bold.
Sub Macro31a()
Range(“D5:D16”).Select
Selection.NumberFormat = “#,##0”
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 36
End Sub
You notice that we refer to Selection many times in the previous sample code. To write more effi-
cient code, you can simply 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. Note that this
macro doesn’t actually select the range at all. This is a key point. In a macro, we can work with a range
without selecting it first.
Sub Macro31a()
With Range(“D5:D16”)
.NumberFormat = “#,##0”
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
Another way you can select a range is by using the Cells item of the Range object.
The Cells item gives us an extremely handy way of selecting ranges through code. It requires only
relative row and column positions as parameters. Cells(5, 4) translates to row 5, column 4 (or
Cell D5). Cells(16, 4) translates to row 16, column 4 (or cell D16).
If you want to select a range of cells, simply pass two items into the Range object. This macro performs
the same selection of range D5:D16:
Sub Macro31a()
Range(Cells(5, 4), Cells(16, 4)).Select
End Sub
Here is the full formatting code using the Cells item. Again, note that this macro doesn’t actually
select the range we are altering. We can work with a range without selecting it first.
Sub Macro31a()
With Range(Cells(5, 4), Cells(16, 4))
.NumberFormat = “#,##0”
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
How to use it
To implement this kind of a macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the code window.
