Office tutorials

Formatting cells and fonts in Excel VBA

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, which will simplify the matter. We can refer once to our object and insert any of them formats. We can format any parameters, exactly as if we were doing it directly in Excel. 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 to record this formatting with a macro recorder and then analyze the written code. In formatting, we can refer to a single cell by referencing Cells () and the whole range using the Range () object . To carry out our exercise, let’s fill the scope of A1: A20 with any text.

 

Font formatting – How to format fonts in VBA

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

If we would like to format more than one font property at the same time, we do not need to write the next repeating block of code. In this case, we use the With clause . As in the first example, we will refer to the selected range. Then, change the font size, make it bold, and set the font to Arial.

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

In VBA, colors can be saved in several different ways. We can use both the official VBA naming preceded by the prefix name vb and the use of RGB colors.

The name of VBA The name of RGB
vbBlack RGB (0, 0, 0)
vbWhite RGB (255, 255, 255)
vbRed RGB (255, 0, 0)
vbGreen RGB (0, 255, 0)
vbBlue RGB (0, 0, 255)
vbYellow RGB (255, 255, 0)
vbMagenta RGB (255, 0, 255)
vbCyan RGB (0, 255, 255)

Formatting cells – How to format cells in VBA

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

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

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.

Excercises (You can enter the solution in a comment)

  1. Write a macro that will perform the following operations on cells: Fills 5 adjacent sheet cells with any text. Then, it will merge, 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.

Leave a Reply

Your email address will not be published.