How to use Relative Reference in Excel Macro (while recording)

In this article, I will show you how to use relative reference in Excel Macro while recording.

When you’re recording your own macros, you must know the concept of relative versus absolute recording modes. Because of the incorrect recording mode, a simple macro could fail to perform the intended job.

Just check out the following two examples. The first example is recorded using Absolute referencing. The second example is recorded using the relative reference option.

how to use relative reference in Excel Macro

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

See at first how I record a Macro using the absolute reference

Select the cell A1. Go to the Developer tab and click on the Record Macro option in the Code ribbon.

Record Macro dialog box appears. I put a name for the Macro (“Macro_Absolute”). And click OK button.

Now I select cell range C3: C8 and fill it with Green background color.

Now I stop recording the Macro clicking on the Stop Recording option.

How is this Macro affecting the worksheet?

Check out the following .gif image. You’re seeing that whatever cell I am in when I click on the Absolute Macro Recording button (run the Macro), it is just filling the cell range C3: C8 with ‘green’ color.

how to use relative reference in Excel Macro

Now let’s check out the Macro code.

Sub Macro_Absolute()
'
' Macro_Absolute Macro
'
'
    Range("C3:C8").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Check out this code line: Range("B10:B15").Select

You’re seeing that with Absolute macro recording, we are referencing/selecting only the cell range C3: C8 in the code.

Let’s now check out Relative Macro Recording

The procedure is almost same as above. I select the cell A1 (you can select any cell) in a worksheet.

In the Developer tab, I click on the Use Relative References option in the Code ribbon. And then I click on the Record Macro button.

In the Record Macro dialog box, I name this Macro as Macro_Relative, and click OK button.

Again, I select the cell range C3: C8 and fill it with ‘green’ background color.

Our macro record is done. I just stop recording.

How this relative macro affects your worksheet?

See this image. You’re seeing that whatever cell I am selecting as the location, it is filling cells two columns right and two rows down.

how to use relative reference in Excel Macro

It’s relative, right?

Let’s check out the code of this simple macro.

Sub Macro_Relative()
'
' Macro_Relative Macro
'
'
    ActiveCell.Offset(2, 2).Range("A1:A6").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Just check out this line of code: ActiveCell.Offset(2, 2).Range("A1:A6").Select

This statement means:

  • From your active cell (whatever cell it is), go 2 rows down and 2 columns right.
  • This cell is now the A1 cell (seems interesting, right?)
  • And now fills cell range A1: A6
Note: Offset(2, 2) denotes: the first value for the row and the second value for the column.

I hope this is crystal clear to you.

So, this is how absolute and relative Macro recording work in Excel.

If you have any feedback on this article, let me know in the comment box.

Happy Excelling 🙂

Read More…


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply