Cell Formatting With Font

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:

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

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
    
   
3. Return to Microsoft Visual Basic

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

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
    
   
3. Return to Microsoft Visual Basic

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:

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 , Italic , or Underline
  • Click the more options button . 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: , , or . 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.

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

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:

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:

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.

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
    
   
3. Return to Microsoft Visual Basic