More on the Cells Property of the Range Object

The Cells property of a Range object provides a nice way to refer to cells relative to a starting cell, or within a block of cells. The following refers to cell F11:

Range("D10:G20").Cells(2,3)

If you want to examine a range with the name SalesData and color any figure under 100 red, you can use the following code:

Sub

ColorCells()

Dim rngSales As Range

Dim lRow As Long, lColumn As Long

'Color cells using Cells property

Set rngSales = Range("SalesData")

For lRow = 1 To rngSales.Rows.Count

For lColumn = 1 To rngSales.Columns.Count

If rngSales.Cells(lRow, lColumn).Value

< 100 Then

rngSales.Cells(lRow, lColumn).Font.

. ColorIndex = 3

Else

rngSales.Cells(lRow, lColumn).Font.

. ColorIndex = 1

End If

Next lColumn

Next lRow

End

Sub

The result is shown in Figure 4-3.

® . tfanjiT'I tie- [. ■:>:

m

_ h

H

"A

B

C 1

D

E 1

F 1

fi !

H 1

I

I

1 ! Jan

Feb Mar

Apr May

-J un

Jul

2 iTotal

1402

1455

1467

1508

1435

1592

1639

3 " Prodi

13

104

7D

183

37

10

174

4 Prod2

177

91

19

12

10

120

71

5 Prod3

177

111

127

33

145

150

164

6 Prod4

88

34

94

151

130

101

31

7 ProdS

7

106

126

116

157

1

61

S ProdS

189

167

186

2

176

190

195

V;Prod7

47

103

36

141

77

130

193

10 'ProdS

181

27

B5

29

104

131

151

11 ProdS

5

182

120

103

181

43

134

12 iProdIO

166

55

155

61

21

168

134

13,Prod11

24

121

172

124

103

145

132

14 Prod12

32

78

153

188

29

197

6

1S Prodi3

166

S3

45

131

47

191

33

15 Prod14

130

138

74

174

158

4

155

17"!

1S :

H t ► H HllCefc

Color Ce) Is '

m ■

Figure 4-3

If you want to count all the cells in an Excel 2007 worksheet, you need to be aware that the Count property of the Range object is a Long Integer type and Cells.Count can't return the value of 17,179,869,184 cells, which exceeds the size of a Long. For compatibility with previous versions of Excel, Count is retained as it was and is supplemented by a new CountLarge property, which is a Variant that can return the larger value.

It is not, in fact, necessary to confine the referenced cells to the contents of the Range object. You can reference cells outside the original range. This means that you really only need to use the top-left cell of the Range object as a starting point. This code refers to F11, as in the earlier example:

You can also use a shortcut version of this form of reference. The following is also a reference to cell F11:

Technically, this works because it is an allowable shortcut for the Item property of the Range object, rather than the Cells property, as described previously:

It is even possible to use zero or negative subscripts, as long as you don't attempt to reference outside the worksheet boundaries. This can lead to some odd results. The following code refers to cell C9:

The following refers to B8: Range("D10")(-1,-1)

The previous Font.Colorlndex example using rngSales can be written as follows, using this technique:

Sub ColorCells2()

Dim rngSales As Range

Dim lRow As Long, lColumn As Long

'Color cells using implied Item property Set rngSales = Range("SalesData")

For lRow = 1 To rngSales.Rows.Count

For lColumn = 1 To rngSales.Columns.Count

If rngSales(lRow,

lColumn).Value

< 100 Then

rngSales(lRow,

lColumn).Font.

.ColorIndex = 3

Else

rngSales(lRow,

lColumn).Font.

.ColorIndex = 1

End If

Next lColumn

Next lRow

End Sub

There is actually a small increase in speed if you adopt this shortcut. Running the second example, the increase is about 5% on my PC when compared to the first example.

0 0

Post a comment