How to: Apply Styles to Ranges in Workbooks


Some code examples in this topic use the this or Me keyword or the Globals class in a way that is specific to document-level customizations, or they rely on features of document-level customizations such as host controls. These examples can be compiled only if you have the required applications installed. For more information, see Features Available by Product Combination.

You can apply named styles to regions within Microsoft Office Excel 2003 workbooks. Excel supplies a number of predefined styles.

The Format Cells dialog box displays all the options you can use when formatting cells, and each of these options is available from within your code. To display this dialog box in Excel, click Cells on the Format menu.

You can use the Styles property of a Workbook object to create, delete, modify, and apply styles to named ranges within the workbook.

To apply a style to a named range

  1. Create a new style and set its attributes.

    Excel.Style style = Globals.ThisWorkbook.Styles.Add("NewStyle", missing);
    style.Font.Name = "Verdana";
    style.Font.Size = 12;
    style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
    style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
    style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
  2. Create a NamedRange control, assign text to it, and then apply the new style.

    Microsoft.Office.Tools.Excel.NamedRange rangeStyles =
        this.Controls.AddNamedRange(this.Range["A1", missing], "rangeStyles");
    rangeStyles.Value2 = "'Style Test";
    rangeStyles.Style = "NewStyle";

Compiling the Code

  • This code must be placed in a sheet class, not in the ThisWorkbook class.

  • This code assumes that the style named NewStyle does not already exist in the workbook.

See Also

Community Additions