Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format issue
I have an Excel Add-In for Office 2007. In the application, I have a need to
color the entire row or column based on application created named ranges. I tried to use Range.Interior.Color to highlight rows or columns for which the user created named ranges from the application. However, the issue is I have no way to restore the color the user previously had. So, I decided to use conditional formating. I union all ranges for which named ranges are created by the user using the add-in. Then I create conditional formating for that range with a simple expression "=TRUE" and then set the interior color in the conditional format. Works great - does not impact the cell format user has. Excel.FormatCondition formatCondition = mappedRange.FormatConditions.Add( XlFormatConditionType.xlExpression, XlFormatConditionOperator.xlEqual, "=TRUE", missing, missing, missing, missing, missing) as FormatCondition; if (formatCondition != null) { formatCondition.StopIfTrue = false; formatCondition.Priority = 1; formatCondition.Interior.ColorIndex = 37; } I have a highlight toggle button in the Add-In ribbon, which basically creates or delete the conditional format. In the Add-in a member variable persists the FormatCondition object that was created. The conditional format object will be deleted when user turn off highlight toggle button. Works fine in most cases, but found an issue. The issue is when user had created conditional format outside of the add-in. The user creates a named range using the application add-in. Set the highlight on from the ribbon. Later, user creates their own conditional format (2D color scale) such that it intersect the range used by the highlight. Now, when user try to remove the highlight, the formatCondition.Delete() raises an exception. When I looked into it, the member variable that had the reference to the formatCondition that created is no more a single item instead it represents a collection. So, I tried to do something like formatCondition.AppliesTo.FormatConditions to loop around all the condition and delete the one I created using the expression test "=TRUE". While I am looping thru, I first hit up on the one user created which is color scale condition, so I tried to access the formatCondition.Type == 2 and right there it raises expection. The reason I found was that the formatCondition.Type is not accessible for the user created one. My question to you is how can I delete the format condition that I programmtically created without impacting the user created format conditions. Your help on this will be much appreciated. -- Sriram.K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format issue | Excel Discussion (Misc queries) | |||
Conditional format issue | Excel Worksheet Functions | |||
Conditional Format issue | Excel Discussion (Misc queries) | |||
Conditional Format Issue | Excel Worksheet Functions | |||
Conditional Format Issue | Excel Worksheet Functions |