Share via

How to: Programmatically Apply Styles to Ranges in Workbooks

  • Article

You can apply named styles to regions in workbooks. Excel supplies a number of predefined styles.

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2013 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

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

To apply a style to a named range in a document-level customization

  1. Create a new style and set its attributes.

    Dim style As Excel.Style = Globals.ThisWorkbook.Styles.Add("NewStyle")

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

    Excel.Style style = Globals.ThisWorkbook.Styles.Add("NewStyle");

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. This code must be placed in a sheet class, not in the ThisWorkbook class.

    Dim rangeStyles As Microsoft.Office.Tools.Excel.NamedRange = _
    Me.Controls.AddNamedRange(Me.Range("A1"), "rangeStyles")

rangeStyles.Value2 = "'Style Test"
rangeStyles.Style = "NewStyle"
rangeStyles.Columns.AutoFit()

    Microsoft.Office.Tools.Excel.NamedRange rangeStyles =
    this.Controls.AddNamedRange(this.Range["A1"], "rangeStyles");

rangeStyles.Value2 = "'Style Test";
rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();

To clear a style from a named range in a document-level customization

  • Apply the Normal style to the range. This code must be placed in a sheet class, not in the ThisWorkbook class.

    Me.rangeStyles.Style = "Normal"

    this.rangeStyles.Style = "Normal";

To apply a style to a named range in an application-level add-in

  1. Create a new style and set its attributes.

    Dim style As Excel.Style = Me.Application.ActiveWorkbook.Styles.Add("NewStyle")

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

    Excel.Style style = this.Application.ActiveWorkbook.Styles.Add("NewStyle");

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 Microsoft.Office.Interop.Excel.Range, assign text to it, and then apply the new style.

    Dim rangeStyles As Excel.Range = Me.Application.Range("A1")

rangeStyles.Value2 = "'Style Test"
rangeStyles.Style = "NewStyle"
rangeStyles.Columns.AutoFit()

    Excel.Range rangeStyles = this.Application.get_Range("A1");

rangeStyles.Value2 = "'Style Test";
rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();

To clear a style from a named range in an application-level add-in

  • Apply the Normal style to the range.

    Dim rng As Excel.Range = Me.Application.Range("A1")
rng.Style = "Normal"

    Excel.Range rng = this.Application.get_Range("A1");
rng.Style = "Normal";

See Also

Concepts

Working with Ranges

NamedRange Control

Global Access to Objects in Office Projects

Optional Parameters in Office Solutions