Excel VBA Formatting Cells and Ranges Using the Macro Recorder

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:


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 1In the developer tab click on Record Macro button:

Record Macro Button in the Developer Tab, Excel, VBA

Step 2: Click Ok:

Macro Recorder Dialog

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:

Select a range of cells and click on format cells

Changing the cells fill color to yellow

Applying a thick border to the exterior and interior of the cells

The result of changing the cells fill color to yellow and applying a thick black border to the interior and exterior sides of the range

Step 4: Stop the macro recorder

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:

The VBA code Generated using the macro recorder of excel

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:


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:


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:


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;@"

See Also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *