Page 167 - Excel Progamming Weekend Crash Course

k540629 ch11.qxd 9/2/03 9:34 AM Page 142

142 Saturday Afternoon

The Application object also has a Cells property. It works similar to the
Worksheet.Cells property but always references the cells on the active
Note worksheet.

Referencing All Cells
If the Cells property is used without any arguments, it references all the cells in the range
(the Range.Cells property) or worksheet (the Worksheet.Cells property); therefore, you
could use the following line of code to change the font style and size of all cells in the spec-
ified worksheet:

Worksheets(“Sales Data”).Cells.Font.Name = “Arial”
Worksheets(“Sales Data”).Cells.Font.Size = 10
Likewise, the following code changes the background color of Range1 to a light purple color:

Range1.Cells.Interior.Color = RGB(220, 220, 255)

Setting cell background color and other aspects of worksheet formatting,
including fonts, are covered in detail in Session 14.
Cross-Ref

Referencing by Row and Column
You can use the Cells property to reference individual cells using the following syntax:
Cells(RowIndex, ColumnIndex)

The RowIndex and ColumnIndex arguments are numerical values that identify the cell by
its row and column. A value of 1 specifies the first row or column within the range and then
increases to the right and downward. Cells(1,1) references the top left cell in the range.
The utility of this version of the Cells property is that it enables you to iterate through
all the cells in a range. In effect, you can treat the range as a two-dimensional array. The
procedure is as follows:
1. Use the Range.Rows.Count property to determine the number of rows in the
range.
2. Use the Range.Columns.Count property to determine the number of columns in
the range.
3. Create a For...Next loop that iterates through all the rows.
4. Inside the first For...Next loop, create another loop that iterates through all the
columns.
5. Inside the inner loop, perform whatever action is required on the current cell.