Conditional Formatting in Excel Add-in or Excel Plugin

Conditional Formatting in Excel Add-in or Excel Plugin

Conditional Formatting

Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data. In this Article I will take your through code using C# VSTO (Visual Studio Tools for Office) and VBA to create conditional formatting dynamically.

Prerequisites

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

Create conditional formatting in Excel

Step-1: Click on Conditional formatting command and select New Rule button

Step -2: Select last option “Use a formula to determine which cells to format” from “Select a Rule Type” section

The UI gets changed as below:

Step -3: Click on Range selector to build formula. In this example I put formula to add rule where if value of “B2” cell is Greater than “C2” then do followings:

  • Make font BOLD
  • Change font color to WHITE
  • Fill cell color to RED.

Step -4: Now press OK and change value in cell “B2” to 99 and hit enter

C# code example:

private void btnConditionalFormatting_Click(object sender, RibbonControlEventArgs e)
{
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;
	oRange.FormatConditions.Add(excel.XlFormatConditionType.xlCellValue,
		excel.XlFormatConditionOperator.xlGreater, "=" + oRange.Address);
	oRange.Font.Bold = true;
	oRange.Font.Color= excel.XlRgbColor.rgbWhite;
	oRange.Interior.Color = excel.XlRgbColor.rgbRed;
}

Run the code and you see a conditional formatting has been added into Rules as shown below:

VBA code example

Sub HeighLightCells()
    'Object to range
    Dim oRange As Range
    'Bind selection
    Set oRange = Selection
    
    oRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=300"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With oRange.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With oRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    oRange.FormatConditions(1).StopIfTrue = False
    
    'CleanUp
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
End Sub

Output

Happy coding!!!

Next: Format table styles in Excel using VSTO C#

Leave a Reply

Your email address will not be published.