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.

score 1 · Answer 1 · answered Aug 08 '12 at 07:46

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

enter image description here

SNAPSHOT 2

enter image description here

Hi Siddharth. As stated my issue is there might be some formats already applied on a that cell and some which might be added after excecution of the code. In this case if i try to delete xlWorkSheet.Cells[1, 1].formatconditions(1).delete(); then it might delete the wrong format. I am not sure that the format i want to delete will be on 1st position. — Ronak Nisar, Aug 08 '12 at 11:50
Agreed and hence when you add a format, store the format number in variable or loop through each formats and match it with your requirements and if that is the right one then delete it — Siddharth Rout, Aug 08 '12 at 11:57
Hi siddharth the above logic is working fine for me if all the conditional formatting are applied through C# code. If some conditional formatting(CF) is already present on that range then after delete it does not delete the CF and it also modifies the applied to range for which delete is called. As of what i understood it changes it to the intersection of the what range is applied though excel with the range delete from code. I am not sure why this is happening. — Ronak Nisar, Aug 17 '12 at 12:48

score 0 · Answer 2 · answered Aug 13 '20 at 19:43

formatConditionObj.Delete();

This doesn't work for me. ... Only the last cells formats is removed.

I am not seeing that. If I hold on to the reference I can call Delete and it works. Here is my test where a range contains differing cells. Then I format, wait, and delete.

Deleting a ConditionalFormat

2 Answers2

Linked