Excel VBA Formatting Cells and Ranges Using the Macro Recorder

Aug 04, 2015 by azurous in Border Style

By formatting cells and ranges you can give your worksheets a better look. Formatting applies to the following:

The number format: Numeric, Date, Scientific …
Text Alignment: Horizontal, Vertical, Orientation …
Text Control: Wrap Text, Shrink to fit, Merge Cells …
Text Direction
Font Type: Calibri, Times new Roman …
Font Style: Regular, Italic …
Font Size: 8, 9, 10 …
Font Color
Font Effects: Strike through, Superscript …
Borders: Style, Color, …
Fill: Color, Pattern …
Protection: Locked, Hidden
…

As you can see there are a lot of different formatting options in excel. Listing all of them along with their syntax and examples would require an entire book. Fortunately excel has provided a tool for figuring out the syntax of the different formatting options, the macro recorder. Using the macro recorder you can figure out how to apply different formattings to ranges.

Jump To:

Formatting Cells and the Macro Recorder
Formatting Cell Borders
Formatting Cell Protection
Formatting Cell Font
Formatting Cell Alignment
Changing Cells Number Format

–

Contents

Formatting Cells and the Macro Recorder:

Below I will explain how to use macro recorder to figure out the required syntax for a specific type of formatting:

Step 1: In the developer tab click on Record Macro button:

Step 2: Click Ok:

Step 3: Apply the desired formatting to a range of cells. In this example I will change the color of the range to yellow and a apply a thick border to the exterior and interior sides of the range:

Step 4: Stop the macro recorder

Step 6: Open the visual basic editor. On the project window you will see a new folder which has been created called “Modules”. In this folder there will be a new file “Macro1”. By clicking on it you will see the generated Code:

For this example the code shown below has been generated:

Sub Macro1() ' ' Macro1 Macro ' ' Range("I11:P16").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

Remember that the macro recorder, records your every action. So if you have the macro recorder on and you start doing random things, the final generated code will become very long. So when you are trying to record a macro try to avoid any unnecessary actions. The first line of code you see is:

Range("I11:P16").Select

You will have to replace “I11:P16” with the range you are trying to modify . Also instead of using the select command you could directly use the range object:

Range("I11:P16").Borders(xlDiagonalDown).LineStyle = xlNone Range("I11:P16").Borders(xlDiagonalUp).LineStyle = xlNone With Range("I11:P16").Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With

I have brought some examples below.

–

Formatting Cell Borders:

The following example creates a border on the left edge of cell A1. The pattern is continuous and thick

With Range("A1").Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With

I have covered this topic in detail in the article below:

Excel VBA, Borders

–

Formatting Cell Protection:

The following example locks and hides cells A1:

Range("A1").Locked = True Range("A1").FormulaHidden = True

–

Formatting Cell Font:

The following code changes the font in cell A1 to arial, bold with size 9, applies strike through and single line underline formatting:

With Range("A1").Font 'font type .Name = "Arial" 'font style .FontStyle = "Bold" 'font size .Size = 9 'uncheck strikethrough .Strikethrough = True 'uncheck superscript .Superscript = False 'uncheck subscript .Subscript = False .OutlineFont = False .Shadow = False 'select single line underscript .Underline = xlUnderlineStyleSingle 'set color to yellow .Color = 65535 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With

For more information about formatting font properties please see the article below:

VBA, Excel Font Formatting

–

Formatting Cell Alignment:

The following code sets the horizontal alignment of cell A1 to left, justifies its vertices alignment. Checks the wrap text and merge cell options and applies a -45 degree orientation:

With Range("A1") 'set the horizontal alignement to left .HorizontalAlignment = xlLeft 'set the verticle alignment to justify .VerticalAlignment = xlJustify 'check the wrap text option .WrapText = True 'apply a -45 deg orientation .Orientation = -45 'check the merge cell option .MergeCells = True End With

For more information about cell alignment please see the article below:

VBA Excel, Alignment

–

Changing Cells Number Format:

The following code changes cells A1’s number format to a date with the mm/dd/yy format:

Range("A1").NumberFormat = "mm/dd/yy;@"