Search code examples
excelvb.netspreadsheetgear

Spreadsheetgear - Add background color via conditional formatting

I'm trying to use conditional formatting to add a salmon background color when a cell's value exceeds a certain threshold. Here's my code:

Dim condition As IFormatCondition = thisRow(0, 6).FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.GreaterEqual, 0.035, Nothing)
condition.Interior.Color = Drawing.Color.FromArgb(230, 184, 183)

The condition is created correctly but for whatever reason, instead of setting the background color to salmon, it uses rgb(192, 192, 192).

What am I doing wrong?

Solution

  • Two possibilities come to mind:

    1. You are using SpreadsheetGear 2012 (V7) or earlier. These older versions of SpreadsheetGear have limited support for Conditional Formatting (CF)--one of which was limited support for 24-bit colors. When you specify an arbitrary 24-bit color for a CF that wasn't located in the workbook's 56-color palette (IWorkbook.Colors), SpreadsheetGear will map the provided color to the closest match within the palette.

      SpreadsheetGear 2017 / V8 vastly improved support for Conditional Formatting, including the ability to use arbitrary 24-bit RGB colors, so upgrading could fix this issue.

    2. You are saving to the older Excel 97-2003 (*.xls) file format. Even if you are using SpreadsheetGear 2017+, if you save to this older file format, the CF will lose the 24-bit color and, again, map to the closest color found in the workbook's 56-color palette. In this case it would be best to save to an Open XML (*.xlsx or *.xlsm) file format, which does have full 24-bit color support.