Formatting cells and fonts in Excel VBA

Last Updated on January 11, 2023 by token

Formatting cells and fonts in VBA – introduction

Cells as well as other elements of the Excel spreadsheet can be formatted from the VBA code level. The formatting itself can be done in 2 ways. The first way is to set each format using a separate code block.

You can also use the With clause to format cells and fonts. You can refer your object and insert any of formats. You can format any parameters, exactly as if you were doing it directly in Excel spreadsheet. If you do not know how to write a block of code to format any cell or its content, the best way to do this is record this formatting with a macro recorder and then analyze the written code.

In formatting, you can refer to a single cell by referencing Cells () or the range of cells using the Range () object . To carry out this exercise, let’s fill the rang of A1: A20 with any text.

Font formatting – How to format fonts in VBA

The easiest way to format fonts, in example by setting its size, is to select the cell, then refer to the font and its specific property, in this case its size. Example is listed below.

'OfficeInside.org
Sub FontFormatingExample()
 Range("A1").Select
 Selection.Font.Size = 20
End Sub

If you would like to format more than one font property at the same time, you don’t need to repeat block of code. In this case, you can use the With clause . As in the first example, you will refer to the selected range. Then, change the font size, make it bold, and set the font to Arial.

'OfficeInside.org 
Sub FontFormatingExample1()

Range("A2").Select
With Selection.Font
.Size = 20
.Bold = True
.Name = "Arial"
End With

End Sub

In this way, you can format any font property in Excel sheet. Below are listed the most important properties that you can use.

'OfficeInside.org
Sub FontFormatingExample2()

Range("A5").Select
With Selection.Font
.Size = 25 'Font size
.Bold = True 'Font bold True/False
.Color = vbBlack 'Font color
.Italic = True 'italic font True/False
.Name = "Arial" 'Font type like Arial or Times New Roman
.Underline = True 'Text underline True/False
End With

End Sub

In VBA, colors can be set in several different ways. You can use both the official VBA naming preceded by the prefix name vb or the range of the RGB colors.

Formatting cells – How to format cells in VBA

In cell interiors, as in the case of fonts, you can refer to a single cell interior property. If you want to change the colors, you can also use the colors described in the previous section.

'OfficeInside.org 
Sub InteriorFormatingExample1()
 Range("A4").Select
 Selection.Interior.Color = 65535
End Sub

Using the With clause, you can change the following cell interior properties:

'OfficeInside.org 
Sub InteriorFormatingExample2()
 Range("A12:A14").Select
 With Selection.Interior
 .Pattern = xlSolid 'cell pattern
 .PatternColorIndex = 1 'xlAutomatic 'color pattern from 1 to 56
 .Color = RGB(0, 0, 255) ' You can set interior color using vbColor or RGB
 .TintAndShade = 0 'You can type tint and shade
 .PatternTintAndShade = 0 'You can type tint and shade pattern
 End With
End Sub

Formatting the selection – How to format the range in VBA

In addition to the fonts and interiors of cells, we can also format the other parameters related to the cell. We can set eg text wrapping or cell merging. A detailed description of the parameters that we can use can be found below.

'OfficeInside.org 
Sub FormatingSelection1()
Range("A15:A20").Select
With Selection
.HorizontalAlignment = xlLeft 'Text aligment left or right: x1Left/xlRight
.VerticalAlignment = xlBottom 'Text aligment top or bottom: xlTop/x1Bottom
.WrapText = False 'Text wrap True/False
.Orientation = -1 'Text orientation -1 auto, 0 vertical, 1 horizontal
.AddIndent = True 'You can add indent: True/False
.IndentLevel = 10 'You can set indent level by typing number of levels
.ShrinkToFit = False 'You can fill text size into cell: True/False
.MergeCells = False 'You can merge cells: True/False
End With
End Sub

All examples ypu can find in this file:

Excercises (You can enter the solution in a comment)

1. Write a macro that will do the following operations on cells: Fills 5 sheet cells with any text. Then set the font size to 24, use the Arial font. Then bold the font. Change the interior color of the cell to green using RGB colors.

More examples of Excel VBA basics can be found in the Excel VBA Course section and  Excel VBA How To section. If you have any questions about this chapter, you can post them on the forum without logging in.