63.

Formatting a Range

Formatting contributes much to the usability of a worksheet. For example, borders can emphasize parts of a report and subtle horizontal bars can make a list easier to read.

Add Borders to a Range

Run Macro

1. In the Visual Basic Editor, click the Run Macro button, select TestRange, click Step Into, and then press F8 to initialize the myRange variable. In the Immediate window, type Set myRange = Range("B2").CurrentRegion and press Enter to assign the range containing the sample list to the variable.

   Tip 

   If you don't have a TestRange macro, see the first two steps of the 'Refer to a Relative Range' section earlier in this chapter.

   Object Browser

   

2. Click the Object Browser button, and select Range in the list of Classes. In the list of Members, select the Borders property. The description indicates that this property returns a Borders object.

3. Click the word Borders in the description box to select the Borders object in the Classes list. In the list of Members, select the LineStyle property. The LineStyle property determines the type of line that will appear as the border.

   The description indicates that the property returns a variant, which means there will not be an Auto List showing you the values you can use. You can, however, use the Object Browser to search for the appropriate values for the LineStyle property.

   

   Search

   

4. In the Search Text box above the Classes list, type LineStyle and click the Search button. In the Search Result pane that appears, select XlLineStyle in the Class list. The Members list shows all the possible constants you can use for the LineStyle property.

   

5. In the Immediate window, type myRange.Borders.LineStyle = xlContinuous and press Enter.

   This adds a continuous border around each cell in the list range. When you assign a value to the LineStyle property of the Borders object, the property changes for the border of each cell in the entire range. The Borders object is actually a collection, and you can select specific borders within that collection.

   

   Hide Search Results

   

6. In the Object Browser, click the Hide Search Results button. Then click the Go Back button (just above the Search button) repeatedly until Borders is again selected in the Classes list. Select the Item property in the Members list. The description shows that to select an item from the Borders collection, you use a value from the XlBordersIndex list.

   Go Back

   

7. In the description box, click XlBordersIndex to see the types of borders you can select.

   The items beginning with xlDiagonal are diagonal lines through each cell in the range. The items beginning with xlEdge are borders around the edge of the entire range. The items beginning with xlInside apply to the borders between cells in the grid. If you want to change all the horizontal borders within the range, you would use the xlInsideHorizonal value.

   The XlBordersIndex class is not really a class. There is no such thing as an XlBordersIndex object. It is, rather, an enumerated list. An enumerated list is used when a property or argument can accept only certain values. An enumerated list allows the object model designer to give each of those values a special name-for example, xlInsideHorizontal. The list of named values can then appear in an Auto List as you type a statement. Enumerated lists are included in the list of Classes, but with a special icon.

8. In the Immediate window, type myRange.Borders(xlInsideHorizontal).LineStyle = xlDouble and press Enter. This doubles the horizontal lines within the range.

   

   If you want to change the border around all edges of a range, you can use the Borders collection four times, once for each edge of the range. To simplify the process, a Range object has a method that changes all four edge borders at once.

9. In the Object Browser, select the Range object in the Classes list. In the Members list, select BorderAround.

   The description shows that the method takes up to four arguments, each of which is optional. The edge borders already have a continuous line style, so to darken the border, all you need to do is change the weight.

10. In the Immediate window, type myRange.BorderAround Weight:=xlThick and press Enter. This changes the edges of the range to a thick border.

    

Borders can emphasize parts of a report. The Borders collection allows you to change all the borders at one time or choose a particular type of border to modify. The BorderAround method is a convenient shortcut for assigning a border to all the edges of a multicell range.

Tip 

To clear all the borders within a range assigned to the myRange variable, you can use the statement myRange.Borders.LineStyle = xlNone.

Format Portions of a Range

To enhance the readability of a list, you might want to apply different colors to various ranges within the list. For example, you might want the background of the list to be light gray, with a medium gray for every third row. You might want to give a different format to the heading row-perhaps a dark gray background with light gray text.

Tip 

If you are not stepping through the TestRange macro, with the current region surrounding cell B2 assigned to myRange, see the first step of the preceding section.

1. In the Object Browser, select the Range class. Then, in the list of Members, select the Interior property.

   In the same way that the Borders property returns an object that controls the formatting of the borders of a range, the Interior property returns an object that controls the formatting of the interior of a range.

2. In the description box, click the word Interior to jump to the Interior object.

   The list of properties includes two properties that change the interior color of a range- Color and ColorIndex. You should always use the ColorIndex property when assigning a color to a range. The ColorIndex property specifies one of the 56 colors in the Fill Color drop-down box. The color that corresponds to each number is arbitrary and has no correlation to the location of the color in the drop-down box. For example, index number for Gray-25% (the lightest gray in the palette) is 15.

   

   Important 

   In principle, the Color property allows you to assign any of over 16 million color values to the interior of the range. In practice, when you assign a value to the Color property, Excel finds the ColorIndex that is the closest approximation to the color you specified. In the following section, you will learn how to change the color of a range to any arbitrary color.

   The ColorIndex and Color properties of the Interior object correspond roughly to the SchemeColor and RGB properties of the Fill object of a graphical shape. The RGB property, however, does allow you to assign any arbitrary color to the object. You can use the Fill object and RGB property only with shapes, not with ranges.

3. In the Immediate window, type myRange.Interior.ColorIndex = 15 and press Enter to change the background of the entire sample list to light gray.

   It will be easier to color every third row darker gray if you assign a simple counting number to a variable. You can first assign 3 to the variable, and then you can add 3 to it for each new row you want to format. The ColorIndex value for Gray-40% (the second lightest available gray) is 48.

4. In the Immediate window, type myRow = 3 and press Enter. Then type myRange.Rows(myRow).Interior.ColorIndex = 48 and press Enter. This darkens the interior of the range in the third row of the list.

   

5. Type myRow = myRow + 3 and press Enter. Then click in the statement that assigns the dark gray and press Enter twice: once to darken the sixth row, and once to add three more to the counter. Execute those two statements two more times to darken the ninth and twelfth rows of the list.

   

6. Type Set myRange = myRange.Rows(1) and press Enter. This changes the range variable to refer only to the first row of the list. You can now change the interior of this range to dark gray. The ColorIndex value for Gray-80% is 56.

7. Type myRange.Interior.ColorIndex = 56 and press Enter. This darkens the first row, making the text unreadable. You can change the font of the first row to light gray.

8. Type myRange.Font.ColorIndex = 15 and press Enter.

   

9. Press F5 to complete the macro.

Colors can enhance the readability of a report. To change the color of the cells in a range, you use the Interior property of the range. To change the color of the font, you use the Font property of the range. In either case, you change the color by assigning a number to the ColorIndex property.

Tip 

To find the ColorIndex value for a color in the palette, record a macro while using the Fill Color box to change the color of a cell.

Change the Colors Available in a Workbook

Sometimes, the 56 standard colors in the workbook palette are not sufficient for your needs. For example, in the formatted list from the preceding section, the gray interiors in the body of the list are dark enough to make the text hard to read. Unfortunately, those are the lightest gray colors available in the standard palette. Fortunately, you can change the colors in the palette to any color you want. You simply assign a new value to the appropriate item from the Colors collection of the workbook object. The RGB function makes it easy to create a color value by designating the red, green, and blue components of a color. Each color component is a value from 0 to 255. Making the red, green, and blue components equal creates a shade of gray.

1. In the Immediate window, type ActiveWorkbook.Colors(15) = RGB(220, 220, 220) and press Enter. This lightens the color of the light gray from the 25% of the standard color to approximately 15%.

2. Type ActiveWorkbook.Colors(48) = RGB(180, 180, 180) and press Enter. This lightens the color of the medium gray from the 40% of the standard color to approximately 30%.

3. Type ActiveWorkbook.Colors(56) = RGB(100, 100, 100) and press Enter. This lightens the color of the dark gray from the 80% of the standard color to approximately 60%.

4. Type ActiveWorkbook.ResetColors and press Enter. This statement changes all the palette colors back to the original values.

5. If you want the lighter grays, re-execute the statements that change the workbook color palette.

The color palette applies to the entire workbook and is saved with the workbook. Changing the color palette gives you a great deal of control over the colors that can appear in your workbook. You can use subtle colors to enhance readability while avoiding the distractions of strong color changes.