Worksheet Formatting with Excel VBA

In addition to formatting numeric data, MS Excel provides general formatting options for data in cells or tables—such as text alignment, font selection, borders, background color, etc. For formatting a range of cells, it is convenient to use the commands located in the Styles group on the Home tab of the Ribbon. Of course, individual cells can also be formatted “manually” using various commands on the Home tab.

Automatic Table Reformatting on Data Change
The Change event of the Worksheet object is triggered when values in a worksheet range are changed. This event allows automatic reformatting of a table when new data is entered.

For example, suppose the range B2:B13 contains potato sales data for the company « Rodnye Prostory ». The goal is to:

• Highlight the maximum sales values in bold red.
• Highlight the minimum sales values in blue.
• Keep other values in black.
• Fill cells with sales above the average with yellow.

This automatic reformatting is implemented using the Change event of the Worksheet object.

Managing Range Borders and the Border Object
The Borders property of a Range object returns a collection of Borders objects, each representing one edge or diagonal of the range. Valid constants for the Borders index include: xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, and xlInsideVertical. Each border is a Border object with properties listed in Table 3.8.

Table 1. Properties of the Border Object

For example, the following code sets the top border of range A2:E2 on Sheet3 as a thick red line, and the bottom border as a medium green dashed line:

Sub DemoBorders()
    Dim rgn As Range
    Set rgn = Range("Лист3!A2:E2")
    With rgn.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .Color = RGB(255, 0, 0)
    End With
    With rgn.Borders(xlEdgeBottom)
        .LineStyle = xlDash
        .Weight = xlMedium
        .Color = RGB(0, 255, 0)
    End With
End Sub

If all border components have the same parameters, you can apply formatting to the entire Borders collection:

Selection.Borders.Color = RGB(0, 0, 255)

The RGB() and QBColor() Functions
In VBA, colors are often specified numerically in hexadecimal. Instead of direct hex codes, it is often easier to use the RGB() and QBColor() functions.

The RGB() function creates a color by mixing red, green, and blue components:

RGB(Red, Green, Blue)

• Red – integer 0–255, red component.
• Green – integer 0–255, green component.
• Blue – integer 0–255, blue component.

Table 2. RGB values for standard colors

The QBColor() function returns 16 basic colors based on the parameter value:

QBColor(color)

The color parameter accepts integers 0–15.

Table 3. QBColor color codes

Characters Object (how to format part of a cell’s content)

The Characters property of the Range object returns a Characters object, which represents a string of the specified length starting from the specified character. It is often used when it is necessary to format not the entire content of a cell, but only part of it.

Characters(Start, Length)

• Start — optional parameter that specifies the number of the first returned character from the given string.
• Length — optional parameter that specifies the number of returned characters.

In the following example, the string « Andrey Garnaev and Lada Rudikova » is displayed in cell A1. The first part of this string (« Andrey Garnaev ») is displayed in bold green font, 16 pt, the second part (« and ») is displayed in italic black font, 12 pt, and the third part (« Lada Rudikova ») is displayed in bold red font, 16 pt.

Formatting part of a cell’s content

Sub CharColor()
    With Range("A1")
        .Value = "Kolo Holo and Mbeu Moyo"        
        .Characters(1, 9).Font.Bold = True
        .Characters(1, 9).Font.Size = 16
        .Characters(1, 9).Font.Color = RGB(0, 255, 0)        
        .Characters(11, 3).Font.Italic = True
        .Characters(11, 3).Font.Size = 12
        .Characters(11, 3).Font.Color = RGB(0, 0, 0)
        .Characters(15, 9).Font.Bold = True
        .Characters(15, 9).Font.Italic = False
        .Characters(15, 9).Font.Size = 16
        .Characters(15, 9).Font.Color = RGB(255, 0, 0)
    End With
End Sub

Font Object (specifying a font)

The Font property of the Range object returns a Font object, which represents the font.

Table 1. Properties of the Font object

For example, in the following code, the range A1:B2 is set to bold red font, size 14 pt:

With Range("A1:B2").Font
    .Size = 14
    .Bold = True
    .Color = RGB(255, 0, 0)
End With

Interior Object (range fill)

The Interior property of the Range object returns an Interior object, which encapsulates data about the fill of a range.

Table 2. Properties of the Interior object

In the following example, a red fill with a blue checker pattern is set for the range A1:D5 on Sheet2 .

Range fill

Sub Inter()
    With Worksheets("Sheet2").Range("A1:D5").Interior
        .Color = RGB(255, 0, 0)           
        .Pattern = xlPatternChecker       
        .PatternColor = RGB(0, 0, 255)    
    End With
End Sub

Setting the Number Format

The NumberFormat property of the Range object sets the number format.
For example, the following instructions set:

• in cell A1 — the General format,
• in cell A2 — the Number format displaying three digits after the decimal point, e.g., 12.000,
• in cell A3 — the Time format with a colon as the separator and two digits for hours, minutes, and seconds, e.g., 02:12:55,
• in cell A4 — the Date format with two digits for the day, three letters for the month, and four digits for the year, e.g., 01 Feb 2011.

Range("A1").NumberFormat = "General"
Range("A2").NumberFormat = "0.000"
Range("A3").NumberFormat = "hh:mm:ss"
Range("A4").NumberFormat = "d mmm yyyy"

Setting the Orientation of Text in a Range

The Orientation property of the Range object sets the angle at which text is displayed in a range.
Allowed values are either an angle of text rotation in degrees from –90 to 90, or one of the following constants:

• xlDownward — left alignment from top to bottom, corresponds to –90°,
• xlHorizontal — horizontal alignment, corresponds to 0°,
• xlUpward — right alignment from bottom to top, corresponds to 90°,
• xlVertical — vertical alignment, no degree equivalent.

For example, in the following code, in cell A1 the text is displayed at an angle of 45°, and in cell B1 at an angle of –45°.

Setting the text orientation angle

Sub Orient()
    With Worksheets("Sheet1")
        ' Taille police
        .Range("A1:B1").Font.Size = 16
        .Range("A1").Orientation = 45
        .Range("A1").Value = "Kolo"
        .Range("B1").Orientation = -45
        .Range("B1").Value = "Holo"
    End With
End Sub