At the end of Conditional Formatting Per-Row Color Scales I said I’d be back with code to selectively copy IconSets and DataBars on a per-row basis as well. It took some doing but it’s now presentable. I learned a lot about programming these types of FormatConditions and I’m going to use this utility to generalize what I learned.
First, here’s a sample of the code in action. Some silly guy has added Icons, Data Bars and Color Scales to the values in Row 1. Now he’s copying only the ColorScales from that first row to the rows below:
I (okay, it’s me) adjust the setting and push the button. The button routine passes the values in the worksheet to the main routine. It’s an expanded version of the code in the previous post, with an addition to delete selected formats. The main procedure is available in the download workbook linked at the end of this post. (The delete code is interesting, and shown later in this post.)
ColorScale, IconSet and DataBar Subroutines
Below are the three subroutines, called from the main procedure, to copy each type of FormatCondition: ColorScales, IconSets and DataBars.
The structure of each of these objects is different. With the exception of the Formula property, ColorScale has no relevant properties in the base object. The properties that need to be copied are all within its ColorScaleCriterion object. IconSets have relevant properties both within the IconSet object and within its IconCriterion object. Finally, DataBars contain no criterion object. All of its relevant properties are set within the base DataBar object.
ColorScale
Here’s the ColorScale subroutine. You can see that the only property in the ColorScale object is the Formula property, which is an interesting one. As far as I can tell this property is only applicable through code. I’ll discuss it more below. It’s only sometimes applicable, and if it’s not it will generate an error when you refer to it. So I surround it with and On Error statement to avoid that.
Dim csTarget As ColorScale
Dim csCriterion As ColorScaleCriterion
Set csTarget = rngTargetSection.FormatConditions.AddColorScale(csSource.Type)
On Error Resume Next
csTarget.Formula = csSource.Formula
On Error GoTo 0
For Each csCriterion In csSource.ColorScaleCriteria
With csTarget.ColorScaleCriteria(csCriterion.Index)
On Error Resume Next
.Type = csCriterion.Type
On Error GoTo 0
On Error Resume Next
.Value = isCriterion.Value
On Error GoTo 0
.FormatColor.Color = csCriterion.FormatColor.Color
.FormatColor.TintAndShade = csCriterion.FormatColor.TintAndShade
End With
Next csCriterion
End Sub
Aside from Formula, the properties that need to be copied are part of ColorScale’s ColorScaleCriterion object. They are Type, and the Color and TintAndShade shade properties of ColorScaleCriterion.FormatColor.
The Type property specifies whether a criterion is based on a number, percent, percentile or formula. Value sets the value to be used for the Type, for example, the 90th percentile or the appropriate formula. These properties all have their counterpart in the conditional formatting dialog, shown here with a formula for the Min and Max values:
I surrounded the Type and Value assignments in an On Error statement because only some of the criteria have them and you’ll get an error on the ones that don’t. Sheesh, this stuff is confusing!
IconSet
Aside from Formula, the IconSet object has three properties that need to be copied: IconSet, ReverseOrder and ShowIconOnly. IconSet determines the color and type of icons and the others should be obvious:
Dim isTarget As IconSetCondition
Dim isCriterion As IconCriterion
Set isTarget = rngTargetSection.FormatConditions.AddIconSetCondition
With isTarget
On Error Resume Next
.Formula = isSource.Formula
On Error GoTo 0
.IconSet = isSource.IconSet
.ReverseOrder = isSource.ReverseOrder
.ShowIconOnly = isSource.ShowIconOnly
For Each isCriterion In isSource.IconCriteria
With .IconCriteria(isCriterion.Index)
.Icon = isCriterion.Icon
On Error Resume Next
.Type = isCriterion.Type
On Error GoTo 0
On Error Resume Next
.Value = isCriterion.Value
On Error GoTo 0
.Operator = isCriterion.Operator
End With
Next isCriterion
End With
End Sub
At the IconCriterion level, there are four properties: Icon, Type, Value and Operator. Icon allows you to change individual icons, just like you can in the user interface. For example, here the gray arrow icon set has been modified to use a green ball in place of the gray up arrow:
Operator sets the relationship of the Icon to the Value. For example, the “>” Operator says to use an up arrow if the cell’s value is greater than the Value property.
DataBar
The DataBar object has no criterion property. All the relevant properties are at the DataBar level. So instead of criteria for the Min and Max, you get the MinPoint and MaxPoint properties. These two made me nervous when I first saw them, as you have to set them via their Modify methods, but that seems to work fine.
Dim dbTarget As Databar
Set dbTarget = rngTargetSection.FormatConditions.AddDatabar
With dbTarget
On Error Resume Next
.Formula = dbSource.Formula
On Error GoTo 0
With .AxisColor
.Color = dbSource.AxisColor.Color
.TintAndShade = dbSource.AxisColor.TintAndShade
End With
.AxisPosition = dbSource.AxisPosition
.BarBorder.Type = dbSource.BarBorder.Type
With .BarColor
.Color = dbSource.BarColor.Color
.TintAndShade = dbSource.BarColor.TintAndShade
End With
.BarFillType = dbSource.BarFillType
.Direction = dbSource.Direction
.MinPoint.Modify newtype:=dbSource.MinPoint.Type, newvalue:=dbSource.MinPoint.Value
.MaxPoint.Modify newtype:=dbSource.MaxPoint.Type, newvalue:=dbSource.MaxPoint.Value
.NegativeBarFormat.ColorType = dbSource.NegativeBarFormat.ColorType
With .NegativeBarFormat.Color
.Color = dbSource.NegativeBarFormat.Color.Color
.TintAndShade = dbSource.NegativeBarFormat.Color.TintAndShade
End With
.PercentMax = dbSource.PercentMax
.PercentMin = dbSource.PercentMin
.ShowValue = dbSource.ShowValue = True
End With
End Sub
There’s a whole bunch of properties besides MinPoint and MaxPoint. About half deal with formatting, including formatting of negative values. All of these are analogous to their similarly-named user interface properties:
Deleting Selected FormatConditions
I wrote a routine to delete only certain types of format conditions from a target range. It gets called before the routine to copy the formats. For example, if the routine is copying DataBar formats, we’ll first delete all the existing ones.
It took longer than I expected to get a workable routine, chiefly because my initial attempts were inpossibly slow. Whereas copying all three types of FormatCondition to 1000 rows takes about a second, deleting formats by looping through the whole FormatConditions collection was taking minutes.
The solution was to break the target range into little ranges of 10 rows each and delete the matching conditions from those ranges. I assume this must simplify Excel’s internal indexing of the FormatConditions collection, whatever that means :). This only takes a couple of seconds for a thousand rows.
The other thing to note is that you have to loop backwards through the FormatConditions – just like deleting any Excel object in a loop – or you’ll get a “Subscript out of Range” error:
DeleteColorScales As Boolean, DeleteDataBars As Boolean, DeleteIconSets As Boolean)
Dim FormatConditionsCount As Long
Dim DeleteIncrement As Long
Dim DeleteRangeStart As Long
Dim DeleteRangeEnd As Long
Dim DeleteDone As Boolean
Dim rngDelete As Excel.Range
Dim objFormatCondition As Object
Dim i As Long
'Break target range into smaller ranges, which makes deletion go many times faster!
DeleteIncrement = 10
DeleteRangeStart = 1
'Min to keep from going past end of target range
DeleteRangeEnd = Application.WorksheetFunction.Min _
(DeleteRangeStart + DeleteIncrement, rngTarget.Rows.Count)
Do While Not DeleteDone
'rngTarget.Parent is the worksheet
Set rngDelete = rngTarget.Parent.Range(rngTarget.Cells(DeleteRangeStart, 1), _
rngTarget.Cells(DeleteRangeEnd, rngTarget.Columns.Count))
FormatConditionsCount = rngDelete.FormatConditions.Count
'Check each format condition's type and call matching routine
'Step backwards or risk "Subscript out of Range"
For i = FormatConditionsCount To 1 Step -1
Set objFormatCondition = rngDelete.FormatConditions(i)
If DeleteColorScales And objFormatCondition.Type = 3 Then
objFormatCondition.Delete
End If
If DeleteDataBars And objFormatCondition.Type = 4 Then
objFormatCondition.Delete
End If
If DeleteIconSets And objFormatCondition.Type = 6 Then
objFormatCondition.Delete
End If
Next i
If DeleteRangeEnd >= rngTarget.Rows.Count Then
DeleteDone = True
End If
DeleteRangeStart = Application.WorksheetFunction.Min _
(DeleteRangeStart + DeleteIncrement, rngTarget.Rows.Count)
DeleteRangeEnd = Application.WorksheetFunction.Min _
(DeleteRangeEnd + DeleteIncrement, rngTarget.Rows.Count)
Loop
End Sub
The Formula Property
As I mentioned above, each of these FormatConditions has a Formula property. I don’t see a match for this property anywhere in the Excel user interface. According to MSDN, it:
Returns or sets a String representing a formula, which determines the values to which the data bar will be applied.
This property is useful to limit the range of values that will display the conditional format. A typical scenario is when you have a range of numbers containing both positive and negative values. You may want to create more than one conditional format for this range of numbers—one for positive values and another for negative values.
Sure enough, if I apply it to a DataBar definition, like so…
… I get this formatting, where the DataBars are only applied to cells with values greater than negative 5:
If there is a way to do this in the user interface, please let me know!
Miscellaneous:
- In my previous post on this, I noted that a recorded macro sets the added conditional format to the first priority and that I was doing the same in my code. However, that’s a bad idea. When copying more than one format it will rearrange their order.
- If you are fooling around with this stuff, you’ll notice that recording a macro that modifies existing conditional formatting produces all the code for that formatting, not just for the modification. This can be annoying or useful, depending.
- The StopIfTrue FormatConition doesn’t apply to these three objects. You can see that they are grayed out in the Excel dialog.
- The ScopeType and PTCondition properties have to do with conditional formatting in pivot tables.
Download!
Here’s a workbook with a working model and all the code.