Change formula in Conditional Formatting

Question

I am trying to Highlight the NamedRange if the selected cells is in the range of the NamedRange. For example consider my Range as $A$1:$D$4 named as myNamedRange. Now i am trying to apply some contitional Formatting on it.

//Declaration
		Microsoft.Office.Interop.Excel.FormatCondition formatCondition = null;
		
		//Function
		void myNamedRange_Selected(Excel.Range Target) //event handler for myNamedRange when selected
        {
			//If user is selecting for the 1st time
            if(formatCondition==null)
			{
				formatCondition = (Microsoft.Office.Interop.Excel.FormatCondition)myNamedRange.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Type.Missing,true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
				
				formatCondition.Interior.Color = 5;
			}
			else //Selecting again after deselect
			{
				formatCondition.ModifyEx(Excel.XlFormatConditionType.xlExpression, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing);
			}
        }

        void myNamedRange_Deselected(Excel.Range Target) //event handler for myNamedRange when deselected
        {
			//If deselected the named range, making formula as false and highligting is removed that is applied above but getting exception as explained below.
            formatCondition.ModifyEx(Excel.XlFormatConditionType.xlExpression, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
        }

Using this code what is the expected behaviour as when i select a cell in range $A$1:$D$4 then the conditional formatting should get true and the cells get highlighted. And if a cell other than $A$1:$D$4 e.g $F$4 is selected then it should be false and highlighting should be removed.

But when i am trying to do this i get "Exception from HRESULT: 0x800A03EC" as error message after formatCondition.ModifyEx(...) statement.

I dont want to delele the format applied and again apply that format. I want to change its value from true to false. And I cannot use any Range also for formula where the true and false will be stored. I need to use this boolean values directly.

Answer 1

I think Excel considers 'false' an invalid expression. It's wouldn't require so much work to delete the format on de-select, and recreate it on reselect.

Answer 2

Hi Joseph. Can u help me with a code snippet how can i achieve this.

Answer 3

Well, I've just tested your code with Excel 2007, and it works for me. Can you tell me how you are deriving myNamedRange? Is it through [VSTOWorksheet].Controls.AddNamedRange?

I've also tested my suggestion to delete/re-create it. It goes like this. In namedRange_Selected:

if (formatCondition == null)
{
formatCondition = (Microsoft.Office.Interop.Excel.FormatCondition)namedRange.FormatConditions.Add(
    Excel.XlFormatConditionType.xlExpression, 
    Type.Missing, 
    true, 
    Type.Missing, 
    Type.Missing, 
    Type.Missing, 
    Type.Missing, 
    Type.Missing);

    formatCondition.Interior.Color = 5;
}

And in namedRange_Deselected:

if (formatCondition != null)
{
    formatCondition.Delete();
    formatCondition = null;
}

Answer 4

Yes i am deriving like that only. This code works fine when there are no Conditional Formats applied already on the range. But if some CF are already applied on that range through excel then if i try to delete the format using the above code. It does not delete the Conditional format. In fact the AppliesTo address of CF applied through excel are changed and my format which is applied through code is not delete.

For e.g Apply any format for Range A1:D4 through excel conditional format. For e.g Use CF for value greater than 10 should have some color. Now suppose my range(namedRange in code) contains address as B2, then my formats which i m trying to apply through my code will get applied. On deselect the formats are not getting deleted and even the address(AppliesTo) for the format applied through excel(which i dont want to touch i.e for values greater than 10) is changed such that it contains Address except B2 of the Range A1:D4.

How can i avoid this?

 

Answer 5

I can confirm the problem with Excel 2007, VSTO 3.0, .NET 4.0, Visual Studio 2010.

If I were a Microsoft employee I'd be looking into it. However, I can't even suggest a workaround. Even deleting the named range doesn't doesn't remove the formatting on deselect. Attempts either to modify or delete the ConditionalFormat (stored at class level) results in 0x800A03EC.

Answer 6

Thanks Joseph for your efforts.  Can anyone please help.

Answer 7

Thanks Joseph for your efforts.  Can anyone please help.

Hi Ronak

I think it might be better if this question were in the more specialized Excel for Developers forum, where you'll find more people with in-depth knowledge of Excel. Do you want a moderator to move the question for you?

---

Cindy Meister, VSTO/Word MVP

Answer 8

Ya kindly move this thread. Thanks in advance.