Color Scale Advanced Excel VBA, C#, VB.Net Code Example

Color Scale Advanced Excel VBA, C#, VB.Net Code Example

Color Scale

Refers to conditional formatting rules. All conditional formatting objects are contained within a FormatConditions collection object, which is an underlying child of a Range collection. You can create a color scale formatting rule by using either the Add() or AddColorScale() method of the FormatConditions collection.

Color scales are visual guides that help understanding data distribution and variation. There are two types of scales can be applied to a range of data either a two-color or a three-color scale. For a two-color scale conditional format, you assign the value, type, and color to the minimum and maximum thresholds of a range. A three-color scale also has a midpoint threshold.

Thresholds are determined by setting the properties of the ColorScaleCriteria object. The ColorScaleCriteria object, which is a child of the ColorScale object, is a collection of all of the ColorScaleCriterion objects for the color scale.

Lets put code around to automate the same. In this article we are using VSTO (Visual Studio Tools for office) in both flavors (C#, VB.Net) and VBA (Visual Basic for Applications) hence following prerequisites are recommended:

Prerequisites

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
  • Excel 2010 or above
  • Create a ribbon designer and put button

C# code example

private void btnColorScale_Click(object sender, RibbonControlEventArgs e)
{
	//Retain user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Add 2 colors scale
	excel.ColorScale oColorScale = oRange.FormatConditions.AddColorScale(2);

	//Set the minimum threshold to red and maximum threshold to blue
	oColorScale.ColorScaleCriteria[1].FormatColor.Color = excel.XlRgbColor.rgbRed;
	oColorScale.ColorScaleCriteria[2].FormatColor.Color = excel.XlRgbColor.rgbBlue;
}

VB.Net code example

Private Sub btnColorScale_Click(sender As Object, e As RibbonControlEventArgs) Handles btnColorScale.Click
	'Retain user selection
	dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Add 2 colors scale
	Dim oColorScale As excel.ColorScale
	oColorScale = oRange.FormatConditions.AddColorScale(2)

	'Set the minimum threshold to red and maximum threshold to blue
	oColorScale.ColorScaleCriteria(1).FormatColor.Color = excel.XlRgbColor.rgbRed
	oColorScale.ColorScaleCriteria(2).FormatColor.Color = excel.XlRgbColor.rgbBlue
End Sub

VBA code example

Sub ColorScale()
    'Retain selection
    Dim oRange As Range
    Set oRange = Selection
    
    'Add 2 colors scale
    Dim oColorScale As ColorScale
    Set oColorScale = oRange.FormatConditions.AddColorScale(ColorScaleType:=2)
    
    'Set the minimum threshold to red and maximum threshold to blue
    oColorScale.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
    oColorScale.ColorScaleCriteria(2).FormatColor.Color = RGB(0, 0, 255)
End Sub

Output

figure 1.0

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.