How to programmatically apply conditional formatting on a cell range with numbers using Excel COM Interop?

Question

In MS Excel you can select a range of cells and apply conditional formatting on this range. Is there a method that does it in C# using Microsoft.Office.Interop.Excel?

Given a range of cells containing numbers, I need to apply Red-Yellow-Green color scale. If there isn't a method for that, does anyone know the formula for the applied colors according to the range of numbers and the number in the cell?

command in excel

output

Does this help: http://msdn.microsoft.com/en-us/library/4zs9xy29.aspx? Also: http://stackoverflow.com/questions/2452417/cell-color-changing-in-excel-using-c-sharp — DonBoitnott, Dec 04 '14 at 13:26
possible duplicate of [How can fill I cells A1:A5 with a color using C#?](http://stackoverflow.com/questions/4871067/how-can-fill-i-cells-a1a5-with-a-color-using-c) — DonBoitnott, Dec 04 '14 at 13:27
No no, I know how to simply apply color. I need conditional formatting — Yona, Dec 04 '14 at 14:41
XLSGen (http://xlsgen.arstdesign.com/) claims to provide an API for programmatically creating conditional formatting. — neontapir, Dec 04 '14 at 15:32
Then perhaps this: http://msdn.microsoft.com/en-us/library/bb404903.aspx — DonBoitnott, Dec 04 '14 at 17:07

score 5 · Accepted Answer · edited May 23 '17 at 12:29

As per the comment by Don, Microsoft provides a full example in C# and VB on how to do conditional formatting in Excel using Microsoft.Office.Interop.Excel: on a given range, use .FormatConditions.AddColorScale() for color, or .FormatConditions.AddIconSetCondition() for icon set conditional formatting

As per SO guidelines, in case the link goes away, here's the essence of applying color formatting, as taken from that link:

// Fill cells A1:A10 with sample data.
targetSheet.get_Range("A1",
paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 1);
targetSheet.get_Range("A2", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 2);
targetSheet.get_Range("A1:A2",
paramMissing).AutoFill(targetSheet.get_Range("A1:A10", paramMissing), XlAutoFillType.xlFillSeries);

// Create a two-color ColorScale object for the created sample data
// range.
cfColorScale = (ColorScale)(targetSheet.get_Range("A1:A10",
    Type.Missing).FormatConditions.AddColorScale(2));

// Set the minimum threshold to red (0x000000FF) and maximum threshold
// to blue (0x00FF0000). Values are in 00BBGGRR format.
cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF;
cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x00FF0000;

Important: Color values used through COM with Excel require the color to be in 00BBGGRR format (first byte always zero). By default, .NET uses AARRGGBB in the System.Drawing.Color classes, so these colors cannot be used directly (as a mnemonic, the COM colors are in alphabetic order: Blue, Green, Red).

As with any Excel interop from .NET, you need to reference the Excel 12.0 Object library and import the Microsoft.Office.Interop.Excel namespace.

How to programmatically apply conditional formatting on a cell range with numbers using Excel COM Interop?

1 Answers1