Page 151 - Excel Progamming Weekend Crash Course

h540629 ch10.qxd 9/2/03 9:34 AM Page 126

126 Saturday Morning

2. Move the cell pointer to the first cell in the column.
3. Open the VBA Editor.
4. Use the Project Explorer to open a module. You can use the module associated with
the worksheet that you put the data in, or you can add a new module.
5. Add the code from the listing to the module.
6. Run the program using one of the techniques you have learned so far.

Listing 10-1 Demonstration of using a relative range to format worksheet cells

Public Sub AlternateRowsBoldface()

‘ Starting at the active cell, formats
‘ cells in the column below with alternate
‘ rows in boldface. Stops when it encounters
‘ an empty cell.

Dim r1 As Range
‘ Set r1 to the active cell
Set r1 = ActiveCell

‘ Loop until an empty cell is found.
Do While r1.Value <> “”
‘ Set the font to boldface.
r1.Font.Bold = True
‘ Get a new range two rows below the current one.
Set r1 = r1.Offset(2, 0)
Loop
End Sub

Empty Cells

A blank cell’s Value property seems to return an empty string, as was used in
Listing 10-1; however, it actually returns the special value Empty. You can test
for this situation using the IsEmpty function:

If IsEmpty(Range(“B2”).Value) Then
‘ cell B2 is blank.
Else
‘ cell B2 is not blank.
End If