Search code examples
c#excel-formulaexcel-interop

C# Conditional Formatting on Microsoft.Office.Interop.Excel.Cells based on cell text

I write a code with C# and Microsoft.Office.Interop.Excel. In generated a drop-down list where one can chose between "okay", "delete", "else". Now I want that the cell becomes red if "delete" is chosen. I tried to do this with Excel.FormatCondition:

Excel.FormatCondition condition = mysheet.get_Range("J2:J10",
    Type.Missing).FormatConditions.Add(Type:Excel.XlFormatConditionType.xlTextString,
    Operator: Excel.XlFormatConditionOperator.xlEqual, Formula1: "=\"delete\"");
condition.Interior.ColorIndex = 3;

With this code I get an error:

System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'

Solution

  • Old question, but hopefully this might help someone else. When you set the type to Type:Excel.XlFormatConditionType.xlTextString, you need to set the String: and TextOperator: parameters and not Operator: and Formula1: as shown in the question.

    Fixing the code:

    FormatConditions.Add(Type:Excel.XlFormatConditionType.xlTextString,
    TextOperator: Excel.XlContainsOperator.xlContains, String: "delete");