Microsoft Office 2000/Visual Basic Programmer's Guide

Using the Cells Property

You use the Cells property to loop through a range of cells on a worksheet or to refer to a range by using numeric row and column values. The Cells property returns a Range object representing all the cells, or a specified cell, in a worksheet. To work with a single cell, you use the Item property of the Range object returned by the Cells property to specify the index of a specific cell. The Item property accepts arguments specifying the row or the row and column index for a cell.

Since the Item property is the default property of the Range object, it is not necessary to explicitly reference it. For example, the following Set statements both return a reference to cell B5 on Sheet1:

Dim rng1 As Excel.Range
Dim rng2 As Excel.Range

Set rng1 = Worksheet("Sheet1").Cells.Item(5, 2)
Set rng2 = Worksheet("Sheet1").Cells(5, 2)

The row and column index arguments of the Item property return references to individual cells beginning with the first cell in the specified range. For example, the following message box displays "G11" because that is the first cell in the specified Range object:

MsgBox Range("G11:M30").Cells(1,1).Address

The following procedure illustrates how you would use the Cells property to loop through all the cells in a specified range. The OutOfBounds procedure looks for values that are greater than or less than a specified range of values and changes the font color for each cell with such a value:

Function OutOfBounds(rngToCheck As Excel.Range, _
                     lngLowValue As Long, _
                     lngHighValue As Long, _
                     Optional lngHighlightColor As Long = 255) As Boolean
   ' This procedure illustrates how to use the Cells property
   ' to iterate through a collection of cells in a range.
   ' For each cell in the rngTocheck range, if the value of the
   ' cell is numeric and it falls outside the range of values
   ' specified by lngLowValue to lngHighValue, the cell font
   ' is changed to the value of lngHighlightColor (default is red).
   Dim rngTemp            As Excel.Range
   Dim lngRowCounter      As Long
   Dim lngColCounter      As Long
   
   ' Validate bounds parameters.
   If lngLowValue > lngHighValue Then
      Err.Raise vbObjectError + 512 + 1, _
         "OutOfBounds Procedure", _
         "Invalid bounds parameters submitted: " _
            & "Low value must be lower than high value."
      Exit Function
   End If
   
   ' Iterate through cells and determine if values
   ' are outside bounds parameters. If so, highlight value.
   For lngRowCounter = 1 To rngToCheck.Rows.Count
      For lngColCounter = 1 To rngToCheck.Columns.Count
         Set rngTemp = rngToCheck.Cells(lngRowCounter, lngColCounter)
         If IsNumeric(rngTemp.Value) Then
            If rngTemp.Value < lngLowValue Or rngTemp.Value > lngHighValue Then
               rngTemp.Font.Color = lngHighlightColor
               OutOfBounds = True
            End If
         End If
      Next lngColCounter
   Next lngRowCounter
End Function

The OutOfBounds procedure is available in the modExcelSamples module in ExcelSamples.xls in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

You can also use a For Each…Next statement to loop through the range returned by the Cells property. The following code could be used in the OutOfBounds procedure to loop through cells in a range:

' Iterate through cells and determine if values
' are outside bounds parameters. If so, highlight value.
For Each rngTemp in rngToCheck.Cells
   If IsNumeric(rngTemp.Value) Then
      If rngTemp.Value < lngLowValue Or rngTemp.Value > lngHighValue Then
         rngTemp.Font.Color = lngHighlightColor
         OutOfBounds = True
      End If
   End If
Next rngTemp