S
Sriram Krish
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.
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.