Make Formatting Super Quick - Record Relative Macros
In a recent blog post we learnt how to create custom formats in Excel and how useful and time saving they can be. You can save a custom format in the default workbook to make it available globally but a lot of people like to have a shortcut key or a button to apply a custom format. To create this, you must record a macro.
Here's a video which demonstrates how to record a relative macro which is a real time saver for formatting different cell ranges. Watch the video combined with an explanation below the video to further understand the difference between recording relative and absolute ranges.
By default, when you record and playback a macro, Excel is very literal and the keystrokes are played back exactly as they were recorded. For example, if you recorded a maco that makes cell A1 bold, italic and underlined, when you play back the macro it will always make cell A1 bold, italic and underlined. You'll no doubt however want to be able to use this macro on different cell ranges. After all, the cells that you want to format will most likely be different every time, it's not always going to be cell A1 that needs to be changed.
Another example is if you start recording while cell B7 is selected, and then you press the Down Arrow key, cell B8 is now selected and that's exactly what is recorded and thus played back. When you later select cell E12 for instance, and play back this macro, you might expect that the macro would move down one cell, to E13, as if you had pressed the Down Arrow key. Instead, when that line of the macro is executed, cell B8 is selected.
The reason this happens is that Excel recorded and memorised your absolute steps. It didn't record the press of the Down Arrow key, but instead recorded the movement from cell B7 to cell B8.
If you instead want your macros to be recorded relatively (so that the macro moves down one cell instead of moving to cell B8), then you need to instruct Excel to do so. You do this by using the Relative Reference tool on the Stop Recording toolbar. Click the tool and all your subsequent actions are interpreted relative to the current selected cell. Click the tool a second time, and you are back to subsequent actions being interpreted absolutely.
It is important that you remember to click the tool before you take an action that is recorded. The tool's stated (on or off) affects only the recording of future actions, not what has been already recorded.