Use Macros To Detect The Formatting Properties Of A Cell In An Excel Spreadsheet

This is a guest article from Yoav Ezer. If you want to guest post on this blog, check out the guidelines here.

Have you ever wanted to write a macro that interacts with your spreadsheets formatting rather than the actual values? Perhaps automatically change font, borders or colors decided by your programming logic.

Let’s work on an example.

When you enter values into a cell, you may have seen that the text flows into neighboring cells. Of course you can reformat the cell, stretch it, or turn on wrapping, but what if you could automatically resize the font of an Excel cell so it always fits?

Here is a clever macro script that resizes a cell’s contents on demand and demonstrates how you can programmatically apply formatting based on macros rules.

If you type into a cell then hit the keyboard combination specified (in this case Ctrl-W), the font is automatically altered to ensure the text fits.

The properties we are working with here are simple: the font.size, the text alignment; .HorizontalAlignment and .VerticalAlignment, but there are many others to check out.

In the macro options we set the keyboard combination in the usual way:

The Macro

How it works is the height of the active cell is checked against a variable we created called “RowElement”. If the height is different, we left align and vertically center the contents of the cell. The macro then checks to see if the height is less than or equal to 30 and proceeds to set the font size to the height minus 4 and set the RowElement to this value plus 1.

If the height is greater than 30 then the size is set to the height + 10.


You might wonder how often this macro would be used on a day to day basis but it does demonstrate how you can use macros to detect the formatting properties of cells and elements, and apply formatting automatically based on those results.

How might you apply this functionality to your work?

About the author : Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software. For more Excel tips from Yoav, join him on Facebook or Twitter

Yoav has contributed a couple of more Excel tips to this site, you can click through the following links and read Yoav’s earlier articles:

1. Advanced Excel If Formulas and Functions
2. How to Work with Date Functions in Excel spreadsheet
3. How to create PowerPoint charts from an Excel Data set.