MVP #18: Using a recorded Macro to repeat an action in Excel

Hi.

In today's special post, I will show you how to record a macro and use it to repeat a conditional formatting for over 800 rows of data. It's so easy you will find many other innovative ways to use a recorded Macro. You'll be able to do the impossible without really writing codes.

Here we go.

I have created a sample data, it's a list of products and the prices from two suppliers. I want a conditional formatting that will indicate the supplier with the least price for each product.

And as you can see above, there are 896 products.

Here's how to do the conditional formatting. Select the prices of the two Suppliers for a particular product, in this case, the first product. 

Goto Conditional Formatting > Icon Sets > Indicators

Here's what you'll get

You'll notice that it has ticked 178 as green and 81 as red. I want the opposite, I want it to mark the lower price as green.

I have to go to Conditional Formatting > Manage Rules > Edit Rule > Reverse Icon Order

(reversed the icon order, notice the difference below)

Done.

Ordinarily, I would copy and paste format to do for the remaining 895 rows. But for this doesn't work out of the box for all conditional formats. 

Here is the output of copy paste format for this case.

You'll notice that it's not doing what I want.

A possible way of fixing this is to change the cell references in the rule from Absolute to Relative (remove all dollar signs).

I did that and remade the copy paste format. 

Result: Not what I wanted.

This is where recording a Macro is the easiest way out. The other option is to do the conditional formatting one by one for each of the 896 rows.

So how to do the Macro recording?
Easy.

Just click on the record Macro button. It is on the lower left of your Excel Window.

You'll need to have the Developer tab enabled in your Excel to see that button. Google can help you. I don't want to make this post unnecessarily long.

A dialogbox comes up once you click the button.

Name the Macro and click on OK

Now do the conditional formatting for the second product.

Once you are done, click on the same Macro button to end the Macro recording.

View the Macro

Goto Developer > Macros

Click Edit. This will take you to the Visual Basic Editor and display the code of the recorded Macro.

What we'll do here is to add two lines of code, telling the Macro to do for rows 2 to 897.

You'll notice that the two lines of code are --
 For i = 2 to 897 
.....
.....
Next

I also edited the Range("B3:C3").Select to Range("B" & i, "C" & i).Select

I run the Macro

It does just what I want!

And I can save it as normal Excel file (macro free) without losing my conditional formatting.

Yes, save as macro-free workbook!

Congratulations! 
You've created a VBA Macro to solve a problem. And now you can see that Excel Macros aren't hard at all. With a little ingenuity you can do amazing things without writing a code (from scratch).

Don't forget to subscribe to my posts via the box way below, that way you won't miss any of my Excel tutorials