VBA FormatConditions – Per-Row ColorScales, DataBars and IconSets

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:

Conditional formatting copied

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.

Sub SetRangeColorScale(rngTargetSection As Excel.Range, csSource As Excel.ColorScale)
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:

Color scale with formula

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:

Sub SetRangeIconset(rngTargetSection As Excel.Range, isSource As Excel.IconSetCondition)
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:

Changed icon

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.

Sub SetRangeDataBar(rngTargetSection As Excel.Range, dbSource As Databar)
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:

Data bar dialog

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:

Sub DeleteFormatConditions(rngTarget As Excel.Range, _
    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…

dbDataBar.Formula = "=A1 > -5"

… I get this formatting, where the DataBars are only applied to cells with values greater than negative 5:

Data bar formula property

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.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>