Single Parameter Range Reference

The shortcut range reference accepts a single parameter as well as two. If you are using this technique with a range with more than one row, and the index exceeds the number of columns in the range, the reference wraps within the columns of the range, down to the appropriate row.

The following refers to cell E10:

The following refers to cell D11:

The index can exceed the number of cells in the Range object and the reference will continue to wrap within the Range object's columns. The following refers to cell D12:

Qualifying a Range object with a single parameter is useful when you want to step through all the cells in a range without having to separately track rows and columns. The ColorCells example can be further rewritten as follows, using this technique:

Sub

ColorCells3()

Dim rngSales As Range

Dim lCell As Long

'Color cells using single parameter range

reference

Set rngSales = Range("SalesData")

For lCell = 1 To rngSales.Count

If rngSales(lCell).Value < 100 Then

rngSales(lCell).Font.ColorIndex =

3

Else

rngSales(lCell).Font.ColorIndex =

1

End If

Next lCell

End

Sub

In the fourth and final variation on the ColorCells theme, you can step through all the cells in a range using a For Each...Next loop, if you do not need the index value of the For...Next loop for other purposes:

Sub

ColorCells4()

Dim rng As Range

'Color cells using For

Each..

.Next loop

For Each rng In Range('

'SalesData")

If rng.Value < 100

Then

rng.Font.Colorlndex =

= 6

Else

rng.Font.Colorlndex =

1

End If

Next rng

End

Sub

0 0

Post a comment