«

»

Jan
16
2012

Handy Excel Macros > Automate Excel Text Formatting

Excel Automation/MacrosWhen working with the Microsoft office documents or spreadsheets we end up doing the formatting so manually every time.

Let us take a real time case. I am need to create a spreadsheet with about 100 titles and 500 entries and i need to copy them from various web pages and e-mails. I want all the titles to be in a specific font, size, color etc and entries below them in a different format.

Now, each time i copy something from somewhere the formatting might change depending on where i copy the contents from.

So i am required to change the font/size/color/cell background etc each time i copy something.

Instead, Imagine i have two presets configured in the excel interface. Each time i copy the content into a cell, i click on a preset button or press a keyboard shortcut and i get my formatting done instantly. It would save a lot your time right?

All you need is few lines of macro to configure the formatting

I have written 2 macros below here as a sample. The first one will format the title and the second macro written for format the normal text. If you notice i am configuring for 5 properties at once (font, text color, cell color, bold and cell alignment).

Again for the regular text below titles and configuring 4 properties. If i tie these macros with buttons at the excel interface or assign keyboard shortcuts then i can call them in one click or through a key board shortcut instantly.

 

Sub FormatTitle()

With Selection.Interior

.ColorIndex = 35

.Font.Bold = True

.Font.Size = 11

.Font.ThemeColor = xlThemeColorLight2

.Font.Name = “Calibri”

.HorizontalAlignment = xlLeft

End With

End Sub

 

Sub FormatParagraph()

With Selection

.Font.Bold = False

.Font.Size = 10

.Font.Name = “Calibri”

.HorizontalAlignment = xlLeft

End With

End Sub

 

See it in action here (It is a sample excel file with macros. Ensure you enable the macros in the workbook to try it)

 

4 comments

  1. Mike says:

    Interesting and helpful. I’d have to take a few minutes learning the designer area of Excel, but doesn’t look too hard with your samples.

    1. electron says:

      Thank you!

  2. Tester says:

    Hi Thanks for you fantastic lesson. But I would like to ask you a question and I would be extremely happy I you can respond me for that. I need to share a excel file with certain set of people. All the cells should be in “Text format”, the format should not be changed at any time by anyone. In order to enter date, they enter the date once and excel converts it into date format and they can just drag the cell to repeat the process. This should not be possible for them. How to do this in macro. Any help would be much appreciated thanks.

    1. electron says:

      It looks like you need to make use of combination of write protected cells and macros. I would recommend you to approach a VBA professional for building this application or post this into stackoverflow

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>