INDIRECT function adds realy dynamism to your formulas.

Suppose you want to sum the values in Col A and currently have 10 items (in cells A2 to A11). You can use the following formula to get the result;
=SUM(A2:A11)

As you add more items you need to update the end cell address from A11.

INDIRECT gives you a way to avoid this.

Let us say you have the last cell address in B2...

Update your formula to;
=SUM(INDIRECT("A2:" & B2))

You can have just the row number of the last cell in B2 (eg. 100) then you can use
=SUM(INDIRECT("A2:A" & B2))

This is same as
=SUM(A2:A100)

Sub colorCells()
' This macro colors the cell background and changes the font color
' for those cells in Column A which have negative values in Column B

Dim lastRow As Long
Dim colToColor As String
Dim colToTest As String
Dim i As Long
colToColor = "A"
colToTest = "B"
lastRow = Cells(Rows.Count, colToColor).End(xlUp).Row()
For i = 2 To lastRow
If Cells(i, colToTest) < 0 Then
Cells(i, colToColor).Select
With Selection.Interior
.Color = 10040319
End With
With Selection.Font
.Color = -16776961

Let us assume that you have duration of your telephone calls in h:mm:ss format in an Excel sheet;
eg.
00:02:30
00:10:17
1:34:12 ('talking' to your partner after forgetting your anniversary? :-)

Now you want to calculate the cost of each call. Let us assume that phone company charges at $ 0.10 for each 30 second (or fraction thereof) interval.

The following formula (in cell C2) will give you the cost for the time in cell A2 with rate given in cell B2
=ROUNDUP(A2*(24*60*2),0)*$B$2 (Absolute reference to the cell containing the rate will help you copy the formula down)