How to: Programmatically apply styles to ranges in workbooks

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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 VSTO Add-in projects for Excel. 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.

    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;

    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

  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.

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

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

    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()

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

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

    this.rangeStyles.Style = "Normal";

    Me.rangeStyles.Style = "Normal"

To apply a style to a named range in a VSTO Add-in

  1. Create a new style and set its attributes.

    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;

    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

  2. Create a Range, assign text to it, and then apply the new style.

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

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

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

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

To clear a style from a named range in a VSTO Add-in

  1. Apply the Normal style to the range.

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

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

See also