PDA

View Full Version : Solved: Quintiles and Conditional Formatting



stanl
06-06-2012, 02:58 AM
I was just asked to place a number of metrics into quintiles and color_code respective cells/rows based on quintile ranking. I accomplished this by creating hlookups to associate a metric with a quintile (1-5) then wrote conditional formulas for each color code (see attached jpeg).

This took a long time and my general question is: where is this kept? For example, if I wanted to change the fill color for quintile 4 I would have to go in manually and edit each rule. Would be nice to be able to write VBA code to accomplish the same

Teeroy
06-08-2012, 05:48 AM
The conditional formatting rules are attached to a Range and you can edit the rules programatically. See the following link for details on how to add, edit and delete them:

http://msdn.microsoft.com/en-us/library/bb286672%28v=office.11%29.aspx

stanl
06-08-2012, 06:05 AM
Thanks; it's a start.

stanl
06-08-2012, 08:00 AM
After a couple of false starts, I enumerated the format conditions for the usedrange of the specific worksheet and it appears I can get/set

FormatConditions.Item(i).Interior.Colorindex

Good early morning workout :bow: Thanks again for the link.

Stan