Monthly Archive for April, 2016

Recording Macros is brilliant! Especially if you are not sure on how to word something initially. If you can’t decide what to use in your code, try recording macros that similar to what you are trying to do and see what is entailed in the code. The obvious next step would be to remove any unwanted or redundant code.

The first step is record a Macro. The best way to do this is click the Record Macro Button which is found on the Status Bar (the green bar at the bottom of your excel sheet).

Then, the Record Macro Dialogue box appears.

Be sure to fill out the Name and Description boxes with as much details as you can to future proof your Macro, as per below.

Click OK. Now you will have to format the table as you would normally in Excel. Excel will record your steps as a Macro. Click the Record Macro button again to stop recording.

Next would be to view the Macros. To do this, Find the View Tab on the Ribbon, go to the Macro command group, and select View Macros.

Click on the Edit button and this will bring up your Macro in Visual Basic. If you look at the screen grab below, you will see a simple Macro for selecting a range of cells and formatting it with bold, italic and adding a background colour.

This should now give you an idea of the sentencing required to create a macro that grabs a group cells, formats it and adds background colour. Now all you have to do is use your VB know how and adapt the code to something more practical and universal that applies to all cells. Just remember to remove any redundant lines (for example .tintandShade = 0 isn’t needed so you can delete it).

Once your happy with your Macro, then add the code you think will work best (like looping or IF statements). By using this simple way of recording Macros and then editing them to your needs you can save yourself a huge amount of time writing code from scratch.

If found this hint and tip useful, you can always check out our Recycle Code tip for more ideas.