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.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
Table of Contents
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.
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:
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.
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:
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
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 🙂