Home

Cell Formatting With Font

 

Introduction to Fonts

A font is an art effect made of designed symbols used to represent letters and other characters on a cell or a group of cells. A font is characterized by various characteristics such as a name, a style and a size.

Microsoft Excel uses some default fonts to display the names of columns and rows on a worksheet. Another font is used by default to show the contents of cells. If you want to use a different font to display text in cells, you can changes these defaults. When changing the default font, in other words if you decide to change the default font, you should use only the most popular fonts that are more likely to be found on your userís computers.

To assist you with setting the fonts of columns, the Ribbon is equipped with a Font section in the Home tab:

Font

The Name of a Font

You can make a worksheet user-friendly by applying some fonts and colors to their content. A font is primarily known by its name. When starting a worksheet, Microsoft Office Excel 2007 applies a default font named Calibri to the cells. If you want, you can change it.

To visually change the font used by a cell or a group of cells:

  • Click the cell to activate it or select a group of cells. On the Ribbon, click Home. In the Font section:
    • Click the arrow of the Font combo box and select the desired font
    • Click the more options button More. This would open the Format Cells dialog box with the Font tab activated. In the Font property page, use the Font combo box to select the desired font
  • Right-click a cell and click Format Cells. Select a group of cells then right-click the selection and click Format Cells. In the Format Cells dialog box, click the Font tab. In the Font property page, use the Font combo box to select the desired font

To programmatically specify the name of a font, refer to the cell or the group of cells on which you want to apply the font, access its Font object, followed by its Name property. Then assign the name of the font to the cell or group of cells.

Practical Learning: Selecting a Font

  1. Change the code as follows (if you do not have the Rockwell Condensed font, use Times New Roman):
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        
        Range("B6") = "Receipt #:"
        Range("G6") = "Order Status:"
        Range("B7") = "Customer Name:"
        Range("G7") = "Customer Phone:"
        
        Range("B9") = "Date Left:"
        Range("G9") = "Time Left:"
        Range("B10") = "Date Expected:"
        Range("G10") = "Time Expected:"
        Range("B11") = "Date Picked Up:"
        Range("G11") = "Time Picked Up:"
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
    
        Range("B15") = "Shirts"
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        
        . . . No Change
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
     
    Georgetown Dry Cleaning Services
  3. Return to Microsoft Visual Basic

The Size of a Font

Besides its name, a font is also known for its size. The size defines how much height and proportional width would be used to represent the characters of the selected font.

To visually specify the font size used by a cell or a group of cells:

  • Click the cell to activate it or select a group of cells. On the Ribbon, click Home. In the Font section:
    • Click the arrow of the Font Size combo box and select the desired size
    • Click the more options button More. In the Font property page of the Format Cells dialog box, use the Size combo box to select the desired size
  • Right-click a cell and click Format Cells. Select a group of cells then right-click the selection and click Format Cells. In the Format Cells dialog box, click the Font tab. In the Font property page, use the Size combo box to select the desired font

When a font is installed, a set of font sizes is created in the Font Size combo box. You can use those numbers but you can also set a new one. To do this, instead of selecting a value in the Font Size combo box, type the desired number and press Enter or Tab. The operating system would calculate the size and apply it.

To programmatically specify the font size of a cell or a group of cells, refer to that cell or the group of cells, access its Font object, followed by its Size property, and assign the desired value to it.

Practical Learning: Setting the Font Size of a Cell

  1. Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        Range("B2").Font.Size = 24
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        Range("B5").Font.Size = 14
        
        . . . No Change
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        Range("B13").Font.Size = 14
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
    
        Range("B15") = "Shirts"
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        Range("H15").Font.Size = 14
        
        . . . No Change
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
     
    Georgetown Dry Cleaning Services
  3. Return to Microsoft Visual Basic

The Style of a Font

Another aspect of the appearance of a font is its style, which is a technique of drawing the characters of the text, depending on the font. This characteristic comes in four options:

Style Example
Regular Regular Text
Bold This text is bold
Italic Italicized section
Underline The words are underlined

To visually change the font style of a cell or a group of cells:

  • Click the cell or select a group of cells on the worksheet. In the Home tab of the Ribbon, in the Font section,
  • Right-click a cell and click
  • Click the cell to activate it or select a group of cells. On the Ribbon, click Home. In the Font section:
    • Click the button that represents the desired style: Bold Bold, Italic Italic, or Underline Underline
    • Click the more options button More. In the Font property page of the Format Cells dialog box, select the desired option in the Font Style combo box
  • Right-click a cell and click Format Cells... Select a group of cells then right-click the selection and click Format Cells. In the Format Cells dialog box, click the Font tab. In the Font property page, select the desired style in the Font Style combo box

You can specify more than one style on a cell or a group of cells. To do this, click the button of the desired style. When a style is valid for a control, when you click that control, the style button is highlighted: Highlighted Bold, Highlighted Italic, or Highlighted Underline. To remove a style, click the undesired button. To add a style to another style, simply click the desired button. Based on this, you can have one, two or three buttons highlighted in the combination of your choice.

To support font styles, the Font object is equipped with various Boolean properties that are Bold, Italic, Underline, and Strikethrough. Therefore, to grammatically specify the font style of a cell or a group of cells, access the cell or the group of cells, access its Font object, followed by the desired style, and assign the desired Boolean value.

Practical Learning: Formatting With Styles

  1. Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        Range("B2").Font.Size = 24
        Range("B2").Font.Bold = True
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        Range("B5").Font.Size = 14
        Range("B5").Font.Bold = True
        
        . . . No Change
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        Range("B13").Font.Size = 14
        Range("B13").Font.Bold = True
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
    
        Range("B15") = "Shirts"
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        Range("H15").Font.Size = 14
        Range("H15").Font.Bold = True
        
        . . . No Change
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
  3. Return to Microsoft Visual Basic
 
 

Text Color

The text in a cell can be improved with color. By default, new text entered in a cell is black. If you want, you can change it.

To visually change the color of text in a cell or the color of text in a group of cells, select the cell or the group of cells. In the Font section of the Home tab of the Ribbon, click the arrow of the Font Color button to display the list of colors and select one:

Font Color

If none of the pre-selected colors suits you, click More Colors... then select or create a color using the Colors dialog box.

The VBA supports colors at different levels. To support colors, the Font object is equipped with a property named Color. To specify the color, assign the desired color to the property. The VBA provides a (limited) list of colors that each can be specified using a named constant. They are:

Color Name Constant Value Color
Black vbBlack &h00  
Red vbRed &hFF  
Green vbGreen &hFF00  
Yellow vbYellow &hFFFF  
Blue vbBlue &hFF0000  
Magenta vbMagenta &hFF00FF  
Cyan vbCyan &hFFFF00  
White vbWhite &hFFFFFF  

Therefore, the available colors are vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and vbWhite. These are standard colors. In reality, a color in Microsoft Windows is represented as a value between 0 and 16,581,375 (in the next lesson, we will know where that number comes from). This means that you can assign a positive number to the Font.Color property and use the equivalent color.

The colors in the Font Color button are represented by a property named ThemeColor. Each one of the colors in the Theme Colors section has an equivalent name in the VBA. If you know the name of the color, assign it to the ThemeColor property.

As another alternative to specify a color, in the next lesson, we will see that you can use a function named RGB to specify a color.

Practical Learning: Specifying the Color of Text

  1. Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        Range("B2").Font.Size = 24
        Range("B2").Font.Bold = True
        Range("B2").Font.Color = vbBlue
        
        Range("B3:J3").Interior.ThemeColor = xlThemeColorLight2
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        Range("B5").Font.Size = 14
        Range("B5").Font.Bold = True
        Range("B5").Font.ThemeColor = 5
        
        . . . No Change
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        Range("B13").Font.Size = 14
        Range("B13").Font.Bold = True
        Range("B13").Font.ThemeColor = 5
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
        
        . . . No Change
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        Range("H15").Font.Size = 14
        Range("H15").Font.Bold = True
        Range("H15").Font.ThemeColor = 5
        
        . . . No Change
        
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
     
    Georgetown Dry Cleaning Services
  3. Return to Microsoft Visual Basic
 
 

 

 

Previous Copyright © 2008-2010 FunctionX, Inc. Next