How to Use Relative Reference in Excel Macro (with Easy Steps)

In this article, I will show you how to use relative references in Excel Macro while recording. You will learn how Excel Macro Relative Reference works after reading the article.
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.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

Download Practice Workbook

Download this practice workbook and practice while going through this article.

4 Quick Steps to Use Relative Reference in Excel Macro

Understanding the use of relative references in Excel macro is easy. For your convenience, I have divided the entire process into 4 easy steps. Let’s check the steps one by one.

Step 1: Activate Macro Recording Feature

The first step is to activate the macro recording feature. This will record the operations that you perform in Excel.

• Select cell A1.
• Then, go to the Developer
• After that, select Use Relative References.
• Finally, select Record Macro.

• The Record Macro box will appear.
• Give a Macro Name.
• Then, click OK.

Now, Excel will record the operations you perform.

Read More: Recording Macro in Excel – Learn with an Example

Step 2: Perform Operation

Now, you can perform the operations you wish. I am just changing the Fill Color of C3:C8.

• Select C3:C8.
• Then, go to the Home
• After that, select the drop-down icon of theme colors.
• Then, choose a color.

• Excel will change the Fill Color.

Step 3: Stop Macro Recording

After performing operations, you need to stop macro recording.

• Go to the Developer
• Select Stop Recording.

Step 4: Run Recorded Macro

Now, it’s time to run the recorded macro.

• Go to the Developer
• Then, select Insert.
• After that, choose a button box.

• Then, draw the box.
• Excel will by default name it Button 1.

• Now, right-click your mouse on the button box.
• After that, select Assign Macro.

• The Assign Macro box will arrive.
• Select Macro_Relative. This is the name of the code I gave earlier.
• Then, click OK.

• Excel will assign the macro to Button 1.
• To run the macro, choose any cell.
• Then, click on Button 1.

• Excel will run the recorded macro.

• Finally, I renamed the button.

The Code:

• You will find the code from the Assign Macro box.
• Select Edit in that box.

• Excel will show you the following code.

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

    End With
End Sub

Read More: How to Assign a Macro to a Button in Excel

Use Absolute Referencing in Excel Macro

Now I will show how Absolute Referencing works in Excel Macro and what the difference is between Absolute Referencing and Relative Referencing.

To apply Absolute Referencing, follow the steps.

• Select A1.
• Then, go to the Developer
• After that, select Record Macro.

• The rest is similar to the previous method.
• The new button box is named Absolute Referencing.

• Now, let’s see how Absolute Referencing works.
• Select E7.
• Then, click on Absolute Reference.

• Excel will keep modifying C3:C8 in Absolute Referencing.

• The code for Absolute Reference is this,

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

Reasons for Relative Reference Not Working

Sometimes the Relative Referencing does not work in Excel. One possible reason is that the “Use Relative References” feature is inactive.
The solution is to activate the button from the Developer tab before performing operations.

Conclusion

In this article, I have discussed Excel Macro Relative Reference. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.