Formatting a Range | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

Formatting contributes much to the usability of a worksheet. Borders and background colors can emphasize parts of a report, and conditional formatting can highlight exceptions within a range. Cell formatting can be combined into cell styles to make the same formatting combinations easy to reuse.

Add Borders to a Range

Borders help to demarcate regions with a block of cells. Sometimes you want to put borders around every cell within a range. Sometimes you want to put a single border around an entire range of cells. Sometimes you want a different border along one side of a range. A Range object has methods and properties to allow you to completely control whatever type of border you need.

In Excel, make a copy of the RevenueFormulas worksheet. In the Visual Basic editor, copy the TestRange macro, give the new one the name AddBorders, and press F8 twice to initialize the myRange variable.

Troubleshooting
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.
In the Immediate window, type Set myRange = Range("B2").CurrentRegion and press Enter to assign the range containing the revenue calculations to the variable.
In the Immediate window, type myRange.Borders.LineStyle =.

As you type each period in the statement, an Auto List displays the available members. After you type the equal sign, no Auto List appears, but you can use the Object Browser to find the available options.
In the Object Browser, in the Search Text box above the Classes list, type LineStyle, and click the Search button.

Search
In the Search Results pane that appears, select XlLineStyle in the Class list.

The Member list shows all the possible constants you can use for the LineStyle property.

XlLineStyle is not really a class, even though it shows up in the list of classes in the Object Browser. There is no such thing as an XlLineStyle 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, xlContinuous. Enumerated lists are included in the list of Classes, but with a special icon.
In the Immediate window, type xlContinuous to finish the statement, and then press Enter.

This adds a continuous border around each cell in the 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.
In the Immediate window, type myRange.Borders.LineStyle = xlNone and press Enter to remove the borders.

The value xlNone does not appear in the enumeration list for LineStyle because it is a global constant that is used by many Excel objects. You can search for it in the Object Browser if you want to see the complete list of global contants.

The Borders object is actually a collection, and you can select specific borders within that collection. In principle, you could change cell borders one at a time, but because putting a border around an entire range is a common operation, there is a special method just for doing that.
In the Immediate window, type myRange.BorderAround Weight:=xlThick and press Enter.

This changes the edges of the range to a thick border. Because Weight is not the first argument, you have to type its name if you leave out LineStyle. Setting the border weight to Thick implies that the line will be continuous.

Suppose that you want a border on the right side of the quantities. To specify a single border, you can use an enumerated name in conjunction with the Borders collection. Auto Lists can help you with the syntax, but you have to be a little tricky.
In the Immediate Window, type

myRange.Borders(xlEdgeRight).LineStyle = xlContinuous, but do not press Enter. Immediately after myRange, type a period ( . ), type Columns(1), and then press Enter.

Once you use the Columns property in a statement, you don’t see any more Auto Lists, but if you temporarily leave out the Columns property, you get Auto Lists for everything else except the line style. Then, after you get the syntax correct for the statement, you can go back and add the Columns property.
In the Immediate window, type

myRange.Rows(2).Borders(xlEdgeBottom).LineStyle = xlContinuous and press Enter. This adds a border under the row of prices.
Press F5 to end the macro. Copy the statements from the Immediate window into the AddBorders macro, and delete the two statements that fill and remove all the borders.

The finished macro should look like this:

Sub SetBorders()   Dim myRange As Range   Set myRange = Range("B2").CurrentRegion   myRange.BorderAround Weight:=xlThick   myRange.Columns(1).Borders(xlEdgeRight).LineStyle = xlContinuous   myRange.Rows(2).Borders(xlEdgeBottom).LineStyle = xlContinuous  End Sub

Create a new copy of RevenueFormulas and test the finished macro.

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.

Format the Interior of a Range

To enhance the readability of a worksheet, you might want to apply different background colors to various parts. For example, you might apply one format to all the cells that contain values that a user can input, and a different format to all cells that contain formulas.

In Excel, create another copy of the RevenueFormulas worksheet. In Visual Basic, copy the TestRange macro, name the new one AddColors, and press F8 twice to initialize the myRange variable.

Troubleshooting
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.
In the Immediate window, type Set myRange = Range("B2").CurrentRegion and press Enter to assign the range containing the revenue calculations to the variable.

In the Immediate window, type myRange.Interior.Color =.

As you type each period in the statement, an Auto List displays the available members. After you type the equal sign, however, no Auto List appears. For the Color property, there is no enumerated list. You can enter any number between 0 (which equals black) and 16777215 (which equals white), so there are literally more than 16 million possible values. This is a major change from previous versions of Excel, where colors in a worksheet were limited to a palette of only 56 colors.

Colors on a computer correspond to the red, green, and blue guns of a cathode ray tube. (Liquid crystal displays use a different technology, but the same component colors.) Visual Basic has an RGB function you can use to specify precise red, green, and blue components, but Excel provides an easier way to specify the color you want: it includes an enumerated list that gives meaningful names to about 140 of the most common colors.

See Also

Excel 2007 also uses theme colors to help you use predefined sets of compatible colors. Theme colors are described in more detail in the section titled “Add a Gradient Fill to a Cell” in Chapter 6, “Explore Graphical Objects.”

In the Immediate window, type rgbMediumVioletRed to complete the statement, and press Enter. (Once you get past rgbM, press Ctrl+Space to get to the middle of the rgb color values.)

The background color of the entire range changes to a medium violet red.

Now that Excel can handle millions of colors, it has a new capability to change how light (the tint) or dark (the shade) a color is without changing the actual color (the hue).
In the Immediate window, type myRange.Interior.TintAndShade = -0.2 and press Enter. The color changes to a slightly darker shade of violet red.

A range object has a special method called SpecialCells that isolates cells within the range based on various attributes. For example, you can reference all the formula cells within the range.
In the Immediate window, type myRange.SpecialCells(xlCellTypeFormulas).Interior.TintAndShade = 0.3 and press Enter.

The block of formulas changes to a lighter tint of violet red. In this range, the formulas form a contiguous block, but SpecialCells can return a range of discontiguous cells as well.

In Excel, you can give a name to a set of formatting characteristics. This is called a cell style. There are several built-in styles in a workbook. One of them is named Input, so that you can use it to format cells that can accept user input-typically cells that contain constants that are numbers.
In the Immediate window, type

myRange.SpecialCells(xlCellTypeConstants, xlNumbers).Style = "Input" and press Enter.

The cells with prices and quantities change to a light tan, with borders around each cell. The constant xlNumbers doesn’t appear in an Auto List, but you can find the list in the Object Browser by searching for SpecialCells.

You can modify the style format in the same way that you can modify a range format directly.
Enter the following two statements in the Immediate window:
```
ActiveWorkbook.Styles("Input").Interior.Color = rgbMediumVioletRed ActiveWorkbook.Styles("Input").Interior.TintAndShade = 0.5 
```
This changes the Input style so that it has a lighter version of the same violet red shade as the rest of the cells. When applying a style to cells that can take input values, you may want to search the entire worksheet for the numeric constants. To do that, you just start with the global Cells property.
In the Immediate window, type

Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Style = "Input" and press Enter.

This adds the Input style to the Discount cell value. If you had hundreds of input cells scattered all over the worksheet, this statement would still find them all. The text labels in the Revenue range are hard to read, with the black text on a dark background. You can use SpecialCells to isolate all the cells that contain text constants.
In the Immediate window, type myRange.SpecialCells(xlCellTypeConstants, xlTextValues).Font.Color = rgbWhite and press Enter.

This changes the font color for the labels to white, but they would look better bold as well. In fact, all the constants within the formula range would look better if they were bold.
In the Immediate window, type

myRange.SpecialCells(xlCellTypeConstants).Font.Bold = True and press Enter.

This changes all the constants within the range store in myRange to bold. By leaving out the second argument to SpecialCells, you get everything that matches the general type. You can also use a special style to clear all the formatting.
In the Immediate window, type Cells.Style = "Normal" and press Enter.

This clears all the formatting, including the number and formats. When you clear formats from a worksheet, what it really does is apply the Normal style to all the cells. By changing the Normal style, you change the default appearance of cells in the workbook.

Press F5 to end the macro. Copy the statements from the Immediate window into the AddColors macro, and delete the statement that clears all the formatting.

The finished macro, ignoring optional line breaks, should look like this:

Sub SetColors()   Dim myRange As Range   Set myRange = Range("B2").CurrentRegion      myRange.Interior.Color = rgbMediumVioletRed   myRange.Interior.TintAndShade = -0.2   myRange.SpecialCells(xlCellTypeFormulas). _   Interior.TintAndShade = 0.3      myRange.SpecialCells(xlCellTypeConstants, xlNumbers). _   Style = "Input"   ActiveWorkbook.Styles("Input").Interior _   .Color = rgbMediumVioletRed   ActiveWorkbook.Styles("Input").Interior _   .TintAndShade = 0.5   Cells.SpecialCells(xlCellTypeConstants, xlNumbers) _   .Style = "Input"      myRange.SpecialCells(xlCellTypeConstants, xlTextValues) _   .Font.Color = rgbWhite   myRange.SpecialCells(xlCellTypeConstants).Font.Bold = True  End Sub

Create a new copy of RevenueFormulas and test the finished macro.

Ranges are powerful objects. They are the essence of Excel. With ranges you can organize information, create formulas, and apply formatting. And you can do all of that with under the control of VBA macros.

CLOSE the Chapter04.xlsm workbook.