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

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

ColorStop

Represents the color stop point for a gradient fill in a range or selection. The ColorStop object enables you to set properties for the cell fill, including the ColorThemeColor, and TintAndShade properties.

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:

C# code example

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

	if (oRange != null)
	{
		excel.Interior oInterior = oRange.Interior;
		//clear color stops
		oInterior.Pattern = excel.XlPattern.xlPatternLinearGradient;
		excel.LinearGradient oGradient = oInterior.Gradient;
		oGradient.Degree = 135;
		oGradient.ColorStops.Clear();

		//Add first color stops
		//1= xlThemeColorDark1
		oGradient.ColorStops.Add(0).ThemeColor = 1;
		oGradient.ColorStops.Add(0).TintAndShade = 0;

		//Add second color stops
		//5= xlThemeColorAccent1
		oGradient.ColorStops.Add(0.5).ThemeColor = 5;
		oGradient.ColorStops.Add(0).TintAndShade = 0;

		//Add third color stops
		//5= xlThemeColorAccent1
		oGradient.ColorStops.Add(1).ThemeColor = 1;
		oGradient.ColorStops.Add(0).TintAndShade = 0;
	}
	else
	{
		MessageBox.Show(@"Select a valid range");
	}
}

VB.Net code example

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

	If not oRange is Nothing Then
		dim oInterior As excel.Interior
		oInterior = oRange.Interior

		'clear color stops
		oInterior.Pattern = excel.XlPattern.xlPatternLinearGradient
		dim oGradient as excel.LinearGradient
		oGradient = oInterior.Gradient
		oGradient.Degree = 135
		oGradient.ColorStops.Clear()

		'Add first color stops
		'1= xlThemeColorDark1
		oGradient.ColorStops.Add(0).ThemeColor = 1
		oGradient.ColorStops.Add(0).TintAndShade = 0

		'Add second color stops
		'5= xlThemeColorAccent1
		oGradient.ColorStops.Add(0.5).ThemeColor = 5
		oGradient.ColorStops.Add(0).TintAndShade = 0

		'Add third color stops
		'5= xlThemeColorAccent1
		oGradient.ColorStops.Add(1).ThemeColor = 1
		oGradient.ColorStops.Add(0).TintAndShade = 0
	Else 
		MsgBox("Select a valid range")
	End If
End Sub

VBA code example

Public Sub ColorStop()
    Dim oRange As Range
    Set oRange = Selection
    
    'Clear color stops
    With oRange.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 135
        .Gradient.ColorStops.Clear
    End With
    
    'Add color stop
    With oRange.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
    With oRange.Interior.Gradient.ColorStops.Add(0.5)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
    
    With oRange.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
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.