Styles Collection Object

                  Properties         Methods        

A collection of all the Style objects in the specified or active workbook. Each Style object represents a style description for a range. The Style object contains all style attributes (font, number format, alignment, and so on) as properties. There are several built-in styles — including Normal, Currency, and Percent — which are listed in the Style name box in the Style dialog box (Format menu).

Using the Styles Collection

Use the Styles property to return the Styles collection. The following example creates a list of style names on worksheet one in the active workbook.

For i = 1 To ActiveWorkbook.Styles.Count
    Worksheets(1).Cells(i, 1) = ActiveWorkbook.Styles(i).Name
Next

Use the Add method to create a new style and add it to the collection. The following example creates a new style based on the Normal style, modifies the border and font, and then applies the new style to cells A25:A30.

With ActiveWorkbook.Styles.Add(name:="bookman top border")
    .Borders(xlTop).LineStyle = xlDouble
    .Font.Bold = True
    .Font.Name = "bookman"
End With
Worksheets(1).Range("a25:a30").Style = "bookman top border"

Use Styles(index), where index is the style index number or name, to return a single Style object from the workbook Styles collection. The following example changes the Normal style for the active workbook by setting its Bold property.

ActiveWorkbook.Styles("Normal").Font.Bold = True