Text Formatting
In Excel, there are 2 types of formatting. One that applies to the text in a cell, and one that applies to the cell itself. To apply formatting to the text, you use the Font object. The Font object allows you to change font face, size, bold, italic etc. For example, the following code makes the selected cell use the Tahoma font:
ActiveCell.Font.Name = "Tahoma"
The other main properties that you can change are listed below:
| Property Name |
| Bold |
| Color |
| ColorIndex |
| Creator |
| Italic |
| Name |
| Size |
| Strikethrough |
| Subscript |
| Superscript |
| Underline |
For the Name and Size properties, you simply specify a font name and size. Other properties such as Italic, are true/false properties:
Range("A1").Font.Italic = True
sets A1's text to italic. The Color property allows you to specify an actual color. You can use the RGB function to form it:
ActiveCell.Font.Color = RGB(255,0,0)
sets the active cells font colour to Red (255 red, 0 green, 0 blue). Alternatively, you can use the ColorIndex property. This lets you specify an item in the workbooks palette (this can be set in the Color tab of the Excel Options dialog).
ActiveCell.Font.ColorIndex = 16
sets the font colour to the 16th item on the colour palette.
Cell Formatting
To apply formatting to a cell, you use the Interior object. For example,
ActiveCell.Interior.Pattern = xlSolid
sets the active cells background pattern to a solid. Its other main properties are shown below:
| Property Name |
| Color |
| ColorIndex |
| Pattern |
| PatternColor |
| PatternColorIndex |
As explained in the last section, using ColorIndex you can specify a color from the standard palette, or using the Color property you can specify an exact colour.
The possible values for the Pattern property are shown below:
|
xlPatternAutomatic
xlPatternChecker xlPatternCrissCross xlPatternDown xlPatternGray16 xlPatternGray25 xlPatternGray50 xlPatternGray75 xlPatternGray8 xlPatternGrid |
xlPatternHorizontal
xlPatternLightDown xlPatternLightHorizontal xlPatternLightUp xlPatternLightVertical xlPatternNone xlPatternSemiGray75 xlPatternSolid xlPatternUp xlPatternVertical |
For example, the following code sets the background colour to blue, the pattern to red horizontal lines:
With ActiveCell.Interior
.Color = RGB(0,0,255)
.Pattern = xlPatternHorizontal
.PatternColor = RGB(255,0,0)
End With
You can also set a cells border using the Borders object and BordersAround object. With these, you can set the following:
| Property Name |
| Color |
| ColorIndex |
| Item |
| LineStyle |
| Value |
| Weight |
The Item property allows you to set a specific border. You can use the BordersAround object to set the 'perimeter' border for a selection of cells
The following code sets the active cell's border to red, and makes the top border thicker:
ActiveCell.Borders.Color = RGB(255,0,0)
ActiveCell.Borders.Item(xlEdgeTop).Weight = xlThick
For more detailed help, type Range("A2").Borders (or another valid range... not ActiveCell as VB doesn't know what that is until the code is being run) and press F1
Comments