Automate Your Excel Formatting: Creating a Conditional Formatting Macro Without Writing Code

So we've covered the basics of what a macro is and how it can benefit your team in our recent article and companion blog, but what about putting these thoughts into practice.

Lets look at one Macro you could start with today.

Conditional Formatting

In this instance we want our macro to format specific cells if sales figures exceed a certain threshold or fall below that threshold.

This new macro will automatically format the report with green highlights for these exceeded values and red highlights for figures below it.

This makes reports more dynamic and insightful at first glance and once recorded once it can be repeated as many times as needed.

Step 1: Record the Macro

1. Open Excel and go to the workbook where you want to create the macro.
2. Click on the Developer tab. (If you don't see it: Go to File > Options > Customize Ribbon, Check the box for Developer and click OK.)
3. In the Developer tab, click Record Macro
4. Name the macro (e.g. "HighlightSales").
5. Choose where to store the macro (e.g. "This Workbook").
6. Click OK to start recording.

Step 2: Apply Conditional Formatting

1. While recording, select the range of cells that contain your sales data (e.g., B2:B100).
2. Go to the Home tab and click on Conditional Formatting.
3. Choose Highlight Cells Rules > Greater Than Enter your sales threshold (e.g., 10000).
4. Choose a green fill colour for cells above the threshold
5. Click OK.
6. Repeat the process by applying another rule for sales figures Less Than your threshold, using a red fill colour.

Step 3: Stop Recording the Macro

1. Once you've applied both conditional formatting rules, go back to the Developer tab.
2. Click Stop Recording.

Step 4: Run the Macro

To run the macro you just recorded

1. Go to the Developer tab and click Macros
2. Select "HighlightSales" and click Run.

This macro will automatically apply the conditional formatting you specified whenever you run it. The Macro Recorder captures all the steps you took while recording and translates them into a macro that can be reused as needed.

This approach allows you to create a macro that automates the process of applying conditional formatting, all without writing any VBA code.

To find out more about how our IT training can help develop your team's skills further see our website here