Deleting a ConditionalFormat

Question

I am trying to add a conditional formatting using C# with the below code.

Microsoft.Office.Interop.Excel.FormatCondition formatConditionObj = null;

formatConditionObj = (Microsoft.Office.Interop.Excel.FormatCondition)myRange
.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, 
Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, 
Type.Missing, Type.Missing);

formatConditionObj.Interior.ColorIndex = 5;

Dynamically i change the range where these formats are applied using

formatConditionObj.ModifyAppliesToRange(NewRange);

Now i want to delete this format which is applied how can this be achieved.

formatConditionObj.Delete();

This doesn't work for me. This does not delete the format for all the cells where it is applied. Only the last cells formats is removed.

I also tried using

formatConditionObj.AppliesTo.Delete();

But it delete other ConditionalFormats also which are applied on that cell.

Note: Some formats are already applied on the cells where this conditinal formatting is applied for e.g some fill color. Even there are some other conditional formats applied on some of the cells. I just want to delete this particular ConditionalFormat(formatConditionObj).

Can anyone help me.

Answer 1

You cannot delete the format conditions like this when you have multiple conditions in a cell. You have to address the conditional format by it's number to delete it.

Consider this example. (TESTED AND TRIED)

The below code creates a new workbook and in sheet 1 creates 2 format conditions in Cell A1. After it creates the 2 conditions, the application will pause by showing you a message box. Go to Excel and manually inspect the conditional formats created. (Snapshot 1). Once done, click on OK in the message box. The code will then delete the condition 1 and then again pause by showing you a message box. Go to Excel and manually inspect the conditional formats. You will notice that there is only one (the second one to be precise) conditional format left. (Snapshot 2)

    private void btnSearch_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

        xlexcel = new Excel.Application();
        xlexcel.Visible = true;

        //~~> Add a File
        xlWorkBook = xlexcel.Workbooks.Add();
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //~~> Create 2 Conditions
        xlWorkSheet.Cells[1, 1].FormatConditions.Add( 1,5,"=5");
        xlWorkSheet.Cells[1, 1].FormatConditions.Add(1, 5, "=10");

        MessageBox.Show("Wait");
        //~~> Now if you check the Excel file, Cell A1 has two conditional formats.
        //~~> See Snapshot 1

        //~~> Delete the first condition
        xlWorkSheet.Cells[1, 1].formatconditions(1).delete();

        MessageBox.Show("Wait");
        //~~> Now if you check the Excel file, Cell A1 has only 1 conditional format.
        //~~> See Snapshot 2
    }

SNAPSHOT 1

SNAPSHOT 2