Conditional Formatting in Excel with C#

c#, excel, vsto

Solution

The following code, adds a conditional formatting to the cell range of D1 to E10

It compares the values D1 = E1 or D2 = E2 respectively. you can set the font color, or color fill on the FormatCondition Object.

FormatCondition format =(FormatCondition)( targetSheet.get_Range("D1:E10",
                Type.Missing).FormatConditions.Add(XlFormatConditionType.xlExpression,
                                                   XlFormatConditionOperator.xlEqual,
                                                   "=$D1=$E1", 
                                                   Type.Missing, Type.Missing, Type.Missing,
                                                   Type.Missing, Type.Missing));
            
            format.Font.Bold = true;
            format.Font.Color = 0x000000FF;

Problem

I need to apply a color to a cell's text if the value is not same as a value in another column. What would be the best approach for it ? The way I can think of is quite expensive. ``` for (int i = 0; i < ColumnARange.Cells.Count; i++) { if (ColumnARange.Cells[i, 1] != ColumnBRange.Cells[i, 1]) { Range currCell = ColumnBRange.Cells[i, 1]; currCell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } } ``` Tried conditional formatting as below, but in vain. ``` FormatCondition cond = ColumnBRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlNotEqual, ColumnARange); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); ``` I am using VSTO,C#

Original source