Absolute Cell References

Excel will default to recording all the cell references as Absolute. These are recorded in A1 notation.
This means that any cell references that are selected are explicitly referred to and you will always get exactly the same result when the macro is played back.
Absolute references are useful when you want to perform the same action in an identical place every time.


 

The second button on this toobar will toggle between recording in Absolute mode and recording in Relative mode.
When this button is pressed, relative references are used and when it is not pressed absolute references are used.


Example

Select cell "B2" before you select (Tools > Macros > Record New Macro).

1) In the Macro Name box type "Absolute".

2) Select the cells "C3:C7".

3) Choose a colour using the Fill Color button on the Formatting toolbar.

4) Select the cells "D3:D7".

5) Choose another colour using the Fill Color button on the Formatting toolbar.

6) Press the bold and italic buttons on the formatting toolbar.

7) Select the cells "E3:E7".

8) Choose another colour using the Fill Color button on the Formatting toolbar.

9) Press the Stop Recording button on the Stop Recording toolbar.


 

If the Stop Recording toolbar did not appear automatically when you started recording you can display it by selecting (View > Toolbars > Stop Recording).
This recorded macro produces the following code:

Sub Absolute() 
    Range("C3:C7").Select
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    Range("D3:D7").Select
    With Selection.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
    End With
    Selection.Font.Italic = True
    Range("E3:E7").Select
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
End Sub



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext