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.

Activate Macro Relative Reference Recording

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

Activate Macro Relative Reference Recording

Now, Excel will record the operations you perform.


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.

Perform Activities


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.

Run Macro Relative Reference Excel

  • 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.

Run Macro Relative Reference Excel

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

Run Macro Relative Reference Excel

  • 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.

Run Macro Relative Reference Excel

  • Finally, I renamed the button.

The Code:

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

Relative Reference Excel Macro

  • 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

Relative Reference Excel Macro


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.

Absolute Reference Excel 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.

Absolute Reference Excel Macro

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

Note: That’s the difference between Absolute Referencing and Relative Referencing in Excel Macro. In Relative Referencing, the position changes upon changing references.

  • When you set the reference A1, it will modify C3:C8.
  • When you set the reference E7, it will modify G9:G14.

But in Absolute Reference, Excel always modifies C3:C8.

  • 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.

Relative Reference Not Working


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.


Read More

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment
  1. As a trainer, I often teach the concept of macros. Some of my delegates really struggle with the concept of relative and absolute. I love this example. For those who are new to macros, it’s a great simple way of explaining the differences. Thanks!

Leave a reply

ExcelDemy
Logo