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.
|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 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)
- 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.