Conditional Formatting Archives

Irregular Banding for Repeated Values

Posted on June 11, 2014 by Doug Glancy

A couple of years ago I came up with a formula to apply irregular banding for repeated values in a table or a pivot table. It uses conditional formatting and this SUMPRODUCT formula:

=MOD(SUMPRODUCT(($B$1:$B1<>$B$2:$B2)*1),2)=0

This array-type formula basically says: Count the number of times the value changes from one cell to the next up to the row where I am (assuming for a moment that I’m a cell). Divide that count by two, and check whether the remainder is 0. This True/False result can then be used to apply the conditional formatting.

The formatting looks like this, where the banding is based on changes in the Animal column:

If the above looks familiar, you may be thinking of this DDOE chestnut:

The difference, aside from my more subdued color scheme, is that Dick’s only starts a new band for the first hamster or what-have-you. It assumes (I assume) that each animal only has one group. Mine assumes bands of hamsters all over the place, and applies a new stripe with every change.

The bad news is my formula doesn’t do well in a long list if you try to delete large numbers of rows. For example, with 20,000 rows if I try to delete all but one, Excel goes into “Not Responding” mode longer than my patience will tolerate (roughly 35 seconds). I don’t know exactly why, but I bet if I re-read this Charles Williams post I would.

The other problem is, as John Walkenbach mentions in Dick’s post, the banding fails if you insert a line before the first row. I tried solving this by using INDEX (and OFFSET, I think) and learned you can’t use it in conditional formatting. Bummer.

The good news is neither of these problems affects its use in pivot tables.

So what to do if you’ve got a 20,000 rows of data you want to band irregularly? The answer, as Tushar Mehta pointed out, is a helper column:

Here’s the formula, starting in A2:

=IF(B2=OFFSET(B2,-1,0),OFFSET(A2,-1,0),SUM(OFFSET(A2,-1,0),1))

It uses the volatile OFFSET function. If you’re going to add or delete rows, OFFSET keeps you from getting #REF! errors or having gaps in the logic.

Then your conditional formatting formula is just: =MOD($A2,2)=0

One cool thing about the helper column banding formula above: It utilizes the fact that SUM ignores any text in the range you’re summing. If you get rid of the SUM and change it to =IF(B2=OFFSET(B2,-1,0),OFFSET(A2,-1,0),OFFSET(A2,-1,0)+1), you’ll get #VALUE! errors because of the text in A1.

Oh wow, look at the time. I’ve got to go restripe my parking lot. With these handy formulas it should go quickly though. See you soon!

Pivot Table Per-Item Color Scales

Posted on December 8, 2013 by Doug Glancy

Let’s take another journey to the nexus of conditional formatting and pivot tables. My latest foray involved adding per-item color-scales to a pivot table, something like this, where the color scale is applied separately to each quarter:

You can see, for example, that the very high sales of $9,999,999 for January in Region 1 affect the color scale for the other cells in Quarter 1. However, the color scale within other quarters are unaffected, and a 99,999 value in Quarter 2 gets the same green that the much higher value did in Quarter 1.

Here’s another view with each month getting its own scale:

Finally, this shows icon sets by region. Notice that although they span each region for all months, the icon sets ignore the subtotals, which is often preferable:

My code allows you to include or include or ignore subtotals in each item’s color scale. I went about this a couple of ways before settling on the PivotTable.PivotSelect method. I learned about this method recently in a comment by Mike Alexander on one of Jeff Weir’s massive DDOE posts. I can’t find any good documentation, but in Excel 2007 forward you can do things like:

pvt.PivotSelect "'Row Grand Total'", xlDataOnly

Select a pivot table’s Row Grand Total area

pvt.PivotSelect "'Region'['East';Data]", xlDataOnly

Select the data rows for the East PivotItem of the Region PivotField

(There is a PivotSelect method in 2003, but these types of statements failed.)

It looks like PivotSelect is the VBA version of clicking, for example, a single subtotal in a pivot table and thereby selecting them all. I figured out the syntax by turning on the Macro Recorder and doing those types of selections:

I’m not crazy about using PivotSelect – I assume all that selecting will slow things down on big pivot tables. On the other hand it seems to work consistently. I had much more complicated code that worked most of the time, but only most.

Here’s the code, which runs in Excel 2007 on:

Sub PivotItemCF(pvtFieldToFormat As Excel.PivotField, IgnoreSubtotals As Boolean)
Dim pvt As Excel.PivotTable
Dim pvtItem As Excel.PivotItem
Dim pvtField As Excel.PivotField
Dim rngTest As Excel.Range

'Lots of selecting!
Application.ScreenUpdating = False

Set pvt = pvtFieldToFormat.Parent
With pvt
For Each pvtItem In pvtFieldToFormat.PivotItems
'A distressing number of possible errors from hidden items.
'If the item has no DataRange ignore it.
On Error Resume Next
Set rngTest = pvtItem.DataRange
If Err.Number = 0 Then
'Specify the Field name in case there are multiple items
'with the same name,for example from Grouping.
'Surround PivotItem names with single quotes in case they have spaces.
pvt.PivotSelect pvtFieldToFormat.Name & "['" & pvtItem.Name & "';Data;Total]", xlDataOnly
'Call routine that does the formatting
FormatRange Selection
End If
On Error GoTo 0
Next pvtItem

If IgnoreSubtotals Then
'Clear the FormatConditions from Row and Column fields
For Each pvtField In pvt.PivotFields
If pvtField.Orientation = xlRowField Or pvtField.Orientation = xlColumnField Then
'I can't figure a test for whether the subtotals are visible
'so just blindly firing away.
On Error Resume Next
.PivotSelect "'" & pvtField.Name & "'[All;Total]", xlDataOnly
If Err.Number = 0 Then
Selection.FormatConditions.Delete
End If
On Error GoTo 0
End If
Next pvtField
.PivotSelect "'Row Grand Total'", xlDataOnly
Selection.FormatConditions.Delete
.PivotSelect "'Column Grand Total'", xlDataOnly
Selection.FormatConditions.Delete
Else
End If
End With

Application.ScreenUpdating = True
End Sub

The FormatRange routine is just Macro Recorder code generated by selecting part of the pivot table and applying a color scale. You can do the same thing for data bars or icons.

In the sample workbook, the code is triggered by a WorkSheet_Change event that fires whenever one of the three yellow data validation cells is changed.

No IFs in Conditional Formatting Formulas

Posted on March 14, 2013 by Doug Glancy

From time to time I’ll answer a question on Stack Overflow about a Conditional Formatting formula that includes an IF statement. Something like this:

=IF(A2=”Something”,TRUE,FALSE)

I’ll politely let them know you don’t need IFs in conditional formatting formulas, and that their formula can be reduced to something like:

=A2=”Something”

I’m going to go out on a limb and say there’s never a need for an IF in a conditional formatting formula.

Why “No IFs?”

What I usually say quickly in my answers is that the IF is implied by the fact that it’s a conditional formula. In other words, when you write the formula you’re telling the conditional formatting:

“If this condition is true, then apply the format, otherwise don’t apply it”. You only need the condition, the rest is a given.

Further, there will never be a reason for a nested IF. Nested IFs only apply when there are more than two possible outcomes. And again, in CF there can only be two: formatting applied or formatting not applied. You might very well, of course, use ORs and ANDs, as those allow you to narrow down the condition:

=AND(A2=”Something”,A3=”Else”)

But it’s still only one condition with two possible outcomes.

VBA FormatConditions – Per-Row ColorScales, DataBars and IconSets

Posted on February 17, 2013 by Doug Glancy

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.

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:

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:

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:

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:

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.

Conditional Formatting Per-Row Color Scales

Posted on February 3, 2013 by Doug Glancy

If you want to compare all the numbers in a range, you can apply a conditional formatting color scale to the entire area and it works just fine. However, sometimes I want to compare the data on a per-row basis. Here’s two examples of data that could use per-row color scales:

The first, above, mixes values and percents. All the percents are red – the low end of the scale – because they equate to numbers between 0 and 1, and they’re getting compared to sales values between 1 and 100.

The second mixes sales from a large and small business. The sales for the small business are all shown as low – red – because they’re generally less than 1/100th of the sales of the large corporation.

In both cases I just want to compare the cells in each row to each other. Doing that, the second example looks like this, showing the relative sales within the company and year:

VBA to Apply Per-Row Color Scales

As far as I can tell, there’s no quick built-in way to apply color-scales (or icon sets or data bars) on a per-row basis. Instead you need to apply them one row at a time. So, of course, I wrote some VBA.

I’ve long been intimidated by Excel 2010’s conditional formatting object model, at least when it comes to non-formula conditions. But one day I answered this StackOverflow post about per-row color scales and decided to dig deeper.

For that answer I turned on the Macro Recorder, applied a color scale, and then called the generated code in a loop for each row. A better approach is to copy an existing row with the color scale you want and paste it over each row.

The simplest version of this is to copy all formatting in a source row and paste it to a target row. However, I’d prefer to grab only the color scale and paste it, ignoring other formats such as borders, text styles and other conditional formats.

If you turn on the Macro Recorder and apply a 3-gradient color scale to a range, you get something that looks like this, with the last five lines repeated two more times:

Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With

Note that it adds a ColorScale and then moves it to the top of the conditional formatting stack with SetFirstPriority.

I discovered a quirk when I tried to modify the above code to do some simple looping through the conditional formatting settings in a range. I tried something like this:

Dim fc As FormatCondition
For Each fc In Selection.FormatConditions
Debug.Print fc.Application.Name
Next fc

That makes sense right? Set an object to a FormatCondition in order to loop through all the FormatConditions. However, if Selection only has color scale conditional formatting, you’ll get a “Type Mismatch” error on the “For Each fc” line. Turns out you need to declare fc as a ColorScale. Then the above code will run and will only cycle through the ColorScale objects.

So here’s the workhorse of my code. It’s called by the main module and loops through a target range, applying the color scale from the source range:

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)
rngTargetSection.FormatConditions(rngTargetSection.FormatConditions.Count).SetFirstPriority
For Each csCriterion In csSource.ColorScaleCriteria
With csTarget.ColorScaleCriteria(csCriterion.Index)
.Type = csCriterion.Type
.FormatColor.Color = csCriterion.FormatColor.Color
.FormatColor.TintAndShade = csCriterion.FormatColor.TintAndShade
End With
Next csCriterion
End Sub

Below is my main routine. It allows you to choose Row or Column orientation, so you can paste the color scales by-column if you wish. The SectionIncrement variable specifies how many rows at a time to paste the color scale, so you could apply the comparison over two or more rows (or columns) at a time.

Note that in this module, objSourceCondition is declared as an object and tested to see if it’s a ColorScale type of FormatCondition:

Sub CopyColorScaleInSections()
Dim rngSource As Excel.Range
Dim rngTarget As Excel.Range
Dim ws As Excel.Worksheet
Dim objSourceCondition As Object 'we'll test for ColorScale
Dim rngTargetSection As Excel.Range
Dim FillDirection As String
Dim IncompatibleRangeError As String
Dim SectionIncrement As Long
Dim SectionsCount As Long
Dim i As Long

'change the settings below to suit
Set ws = ActiveSheet
Set rngSource = ws.Range("B2:E2")
Set rngTarget = ws.Range("B3:E7")
FillDirection = "Rows"
SectionIncrement = 1

'deletes all existing formats
'you might want to change to just delete
'ColorScales, but for demo purposes
'this works well
rngTarget.FormatConditions.Delete
'checks whether the settings above work together
If Not CompatibleRanges(rngSource, rngTarget, SectionIncrement, _
FillDirection, IncompatibleRangeError) Then
MsgBox IncompatibleRangeError, vbOKOnly + vbExclamation
GoTo exit_point
End If

'determine how many sections of rows or columns
'we'll be pasting over
If FillDirection = "Rows" Then
SectionsCount = rngTarget.Rows.Count / SectionIncrement
ElseIf FillDirection = "Columns" Then
SectionsCount = rngTarget.Columns.Count / SectionIncrement
End If

For i = 0 To SectionsCount - 1
'set an individual section to be pasted over
If FillDirection = "Rows" Then
Set rngTargetSection = rngTarget((i * SectionIncrement) + 1, 1) _
.Resize(SectionIncrement, rngTarget.Columns.Count)
ElseIf FillDirection = "Columns" Then
Set rngTargetSection = rngTarget(1, (i * SectionIncrement) + 1) _
.Resize(rngTarget.Rows.Count, SectionIncrement)
End If
For Each objSourceCondition In rngSource.FormatConditions
'test if it's a ColorScale - 3
If objSourceCondition.Type = 3 Then
SetRangeColorScale rngTargetSection, objSourceCondition
End If
Next objSourceCondition
Next i

exit_point:
End Sub

Obviously, when you do this you end up with a passel of conditional formatting rules, so don’t be surprised!

Here’s the function, called from the main routine above, that checks whether the source and target ranges are compatible:

Function CompatibleRanges(rngSource As Excel.Range, rngTarget As Excel.Range, _
SectionIncrement As Long, FillDirection As String, _
ByRef IncompatibleRangeError As String) As Boolean

'no #DIV/0
If SectionIncrement = 0 Then
IncompatibleRangeError = _
"You can't use an increment of 0"
GoTo exit_point
End If
'can't specify a SectionIncrement bigger than the target range
If (FillDirection = "Rows" And rngTarget.Rows.Count < SectionIncrement) Or _
(FillDirection = "Columns" And rngTarget.Columns.Count < SectionIncrement) Then
IncompatibleRangeError = _
"Target range must have at least" & vbCrLf & _
SectionIncrement & " rows."
GoTo exit_point
End If
'target range rows or columns must be
'evenly divisible by the SectionIncrement
If (FillDirection = "Rows" And rngTarget.Rows.Count Mod SectionIncrement <> 0) Or _
(FillDirection = "Columns" And rngTarget.Columns.Count Mod SectionIncrement <> 0) Then
IncompatibleRangeError = _
"Target range " & FillDirection & " must be" & vbCrLf & _
"evenly divisible by " & SectionIncrement & "."
GoTo exit_point
End If
'target range width or height has to match
'source range width or height
If Not (rngSource.Rows.Count = rngTarget.Rows.Count Or _
rngSource.Columns.Count = rngTarget.Columns.Count) Then
IncompatibleRangeError = _
"Source and Target ranges must have" & vbCrLf & _
"either the same number" & vbCrLf & "of rows or columns."
GoTo exit_point
End If

exit_point:
CompatibleRanges = IncompatibleRangeError = ""
End Function

I’ve run this code successfully on up to 10,000 rows. It took about 7 seconds. I did notice that deleting 9,900 of those rows afterwards takes a while, and that the workbook can then act sluggish until it’s saved. I’m not sure what the issue is.

No download right now, but I’m planning to whip up a workbook that expands this to IconSets and DataBars and whatever else I can cram in there. So look for that in a week or so.

Conditional Formatting Color Scales Based on Other Cells

Posted on January 23, 2013 by Doug Glancy

With formula-based conditional formatting, it’s pretty easy to base the formats on other cells in the workbook, simply by referring to those cells in the formula. However, it’s more complicated if you want color scales derived from values in another range. In this post I discuss two ways to base color scales on another range. The first uses the camera tool while the second is a VBA subroutine that mimics conditional formatting.

Below is an example of what I’m talking about. The color formatting isn’t based on the values in the pivot table. Instead, it reflects the values in the second table, each cell of which contains the difference from the previous year in the pivot table. The colors range from red at the low end to green at the high end:

ormat with camera tool 1

So, here’s my two approaches to doing this:

Using the Camera Tool

This method uses Excel’s under-publicized camera tool, which creates a live picture linked to a group of cells. In this case the formatting is applied to a pivot table, but you can do it with any range. Here’s the steps:

Create the range of formulas that you’ll base the conditional formatting on.
Format the numbers in that range to be invisible, by using a custom format of “;;;”. All you want to see is the conditional formatting.
Use the camera tool to take a picture of the entire pivot table and paste it over the range you just created, lining up the conditionally formatted cells. Set the picture to be completely transparent, using the “no fill” setting. This way you can see through the picture to the conditionally formatted cells underneath.

The result will be like the illustration below. The source pivot table is in rows 11 to 18, and you can see that the picture starting in row 2 is linked to it. The cells underneath the picture contain the formulas referring to the pivot table. The conditional formatting is based on these cells, whose text is invisible because of the custom format.

format with camera tool 2

One thing to be aware of is that the picture doesn’t update until there’s a worksheet recalculation. You may have to force recalculation with F9 to have the picture update.

For one project I augmented this method by writing code that let me toggle back and forth between the values in the pivot table and the values the conditional formatting is based on.

Using VBA to Create “FauxMatting”

As the heading implies, this method attempts to replicate conditional formatting using VBA. The following subroutine takes two ranges – a source and a target range – as its arguments. It finds the highest and lowest values in the source range. It assigns each of those values a color in a scale from green to red, with white in the middle. This is done by dividing the range of values source values into 255 increments. The colors are then assigned to the target range:

Sub ConditionalFauxmatting(rngSource As Excel.Range, rngTarget As Excel.Range)
Const NUMBER_OF_INCREMENTS As Long = 255
Dim MinValue As Double
Dim MaxValue As Double
Dim ScaleIncrement As Double
Dim ScalePosition As Long
Dim var As Variant
Dim CellColor() As Long
Dim i As Long, j As Long

If Not (rngSource.Rows.Count = rngTarget.Rows.Count And rngSource.Columns.Count = rngTarget.Columns.Count) Then
MsgBox "Source and Target ranges must be" & vbCrLf & "same shape and size"
GoTo exit_point
End If
MinValue = Application.WorksheetFunction.Min(rngSource.Value)
MaxValue = Application.WorksheetFunction.Max(rngSource.Value)
'divide the range between Min and Max values into 255 increments
ScaleIncrement = (MaxValue - MinValue) / NUMBER_OF_INCREMENTS
'if all source cells have the same value or there's only one
If ScaleIncrement = 0 Or rngSource.Cells.Count = 1 Then
rngTarget.Cells.Interior.Color = RGB(255, 255, 255)
GoTo exit_point
End If
'assign all the values to variant array
var = rngSource.Value
ReDim CellColor(UBound(var, 1), UBound(var, 2))
For i = LBound(var, 1) To UBound(var, 1)
For j = LBound(var, 2) To UBound(var, 2)
'the scale position must be a value between 0 and 255
ScalePosition = (var(i, j) - MinValue) * (1 / ScaleIncrement)
'this formula goes from blue to red, hitting white - RGB(255,255,255) at the midpoint
CellColor(i, j) = RGB(Application.WorksheetFunction.Min(ScalePosition * 2, 255), _
IIf(ScalePosition < 127, 255, Abs(ScalePosition - 255) * 2), _
IIf(ScalePosition < 127, ScalePosition * 2, Abs(ScalePosition - 255) * 2))
Next j
Next i
'assign the colors stored in the array
'to the target range
With rngTarget
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
.Cells(i, j).Interior.Color = CellColor(i, j)
Next j
Next i
End With

exit_point:
End Sub

The result looks like this:

format with VBA

I’m not sure how practical this is, but it was fun to figure out! Obviously, you’d want to tie this to a worksheet or pivot table event to update the formatting when the values change.

Here’s a workbook demonstrating these two methods.

Unified Method of Pivot Table Formatting

Posted on January 13, 2013 by Doug Glancy

In preparation for my big annual reporting push I’ve developed a (partial) Unified Method of Pivot Table Formatting. My motivation was to define a system that allows me to copy pivot tables as values while preserving the formatting, and that increases formatting flexibility. Simply stated the Unified Method is:

“Use Conditional Formatting for Everything, and Apply Every Conditional Format to the Entire Pivot Table.” *

That’s right. No built-in “PivotTable Styles,” no field-level conditional formats, and no more just selecting the whole table, drawing a border around it and hoping “Preserve cell formatting on update” works.

* Excel 2007 and later. Applies only to formula-based conditions, e.g., not to color scales. Other restrictions apply.

Formatted Pivot Table

Above is a section of a world population pivot table. It has the following conditional formats:

The header row is colored orange with a black border around each cell.
There’s a black border to the left and right of the table.
Subtotal rows are bold with a black border above and below but no interior border, and are the same color as the header row.
Detail row cells are surrounded by a light gray border.
There are no borders between columns A and B, as Column A is only one pixel wide and flops over into Column B, as with “Oceania Total.”
Country rows have alternate banding by country.
State/Province rows’ population number font is gray

That’s quite a list, I think you’ll agree. In the past I might have used regular formatting, pivot table styles and field-level conditional formatting. I’d like to avoid all of those approaches, for the following reasons:

Regular Formatting: By “regular formatting” I mean something like selecting the whole pivot table and applying an outside border. This requires the pivot table setting “Preserve cell formatting on update” to be turned on. I’ll admit I’ve never mastered the quirks of this setting, so I’d like to just avoid it.

I’ve already found things, like header row word-wrapping, that may make me relent on this one.

Built-in PivotTable Styles: I really don’t like the built-in pivot table styles, for a few reasons:

The text and cell colors are ugly to the point of unusability. They almost always need modification.
Modifying them is a pain. The names for pivot table parts are weird. For example, what’s a “First Row Stripe,” and how does “Column Subheading 2” compare to “Subtotal Column 2?” So I do a lot of guessing and backing out of the dialog to see if I guessed right. Very clunky.

Pivot Table Modify Style Dialog

Finally, if you copy the pivot as values these formats disappear, although John Walkenbach has a solution for that.

Field-level Conditional Formatting: The field-level conditional formatting that became available in Excel 2007, and that I discuss in Re-Apply Pivot Table Conditional Formatting, is certainly better than the pivot table styles. But, again, the formats disappear when you copy the pivot table as values. And you can only use them to format the value fields of a pivot table, so for something like alternate row banding that includes row labels you need to apply the rule twice.

THE “UNIFIED” APPROACH
So, instead of the approaches above, I apply every conditional format to the entire pivot table. I use the ModifyAppliesToRange method, as discussed in the post linked above, to re-apply conditional formatting to the entire pivot table when it’s refreshed. This keeps all the formatting intact when I copy the table as values. It also allows me to easily apply formatting to specific columns and rows.

Note that in the Extend Pivot Table Conditional Formatting Post I only dealt with the rows of the pivot table that had data. In the example file at the end of this post I’ve extended the code to include the entire pivot table.

Two Examples
Since I’m applying the conditional formatting to the whole pivot table, the conditions sometimes need to specify row or column numbers. For example, I only want to gray the text for State/Province rows in columns D and E. That condition looks like this:

=AND(COLUMN(A1)>=4,$C1<>"")

This simply says if column C is blank, gray the text from Column D to the right. I could also specify less than or equal to 5 (column E), but since the conditional formatting is limited to the pivot table that’s not necessary.

Another part that was fiddly is not showing borders between columns A and B. To do this requires two formulas, one to negate column A’s right border, another to blank out column B’s left border. Here’s the formula and setup for column A:

No Border Formatting

The order of the rules is very important with this and other conditions. These “no-border” formats need to be before the formats with the borders.

A Couple More Things

I found this post harder to write than most. Although I think this is an interesting and helpful approach, I don’t know how clear I’ve been. If you have any questions, let me know.

It’s worth restating that the Unified Method of Pivot Table Formatting really only works for Excel 2007 onwards. Earlier versions limit you to three conditional formats in a given cell. Also, it only works for formula-based conditional formatting, i.e., not for color scales, icons, etc.

You can download a workbook with the pivot table shown above. It also includes the code to extend the conditional formatting to the whole table after it’s refreshed.

Preview Excel Custom Formats

Posted on September 16, 2012 by Doug Glancy

(Enter a format in column A and something in column B to preview the format.)

I was thinking of doing a simple post (hah!) on using Excel’s TEXT function to transform numbers to text, in order to match them to ID-type numbers from databases. One of my recurring work tasks is matching a host of possible school IDs from databases (text) to those in spreadsheets (numeric). I frequently use formulas like:

INDEX(tblFromCsv[School_Name],MATCH(TEXT(Numeric_ID,"0"),tblFromCsv[Text_Id],0))

Then recently a post here was featured on Chandoo’s site, along with some from other blogs – increasing my lifetime page views by about 30%. Searching his post for complimentary comments, I saw a couple along the lines of “Thanks for the great links, especially the one from Bacon Bits.”

Mike’s post is indeed a a beauty. It shows how to use custom number formats and create a percentage format like that shown in the first two rows of the interactive workbook above. It got me thinking about what formats you can specify in the TEXT function. It turns out anything you can enter as a custom format works for the TEXT’s Format argument, with very similar results. For example, this formula will format whatever’s in A1 with a format specified in B1:

=TEXT(A1,B1)

This got me thinking about creating a utility that shows the results of any custom format. Just like what you could get by using Excel’s custom format dialog or one line of VBA code, only a lot more work :). Although to be fair, Excel’s custom format dialog doesn’t show color:

The reason TEXT doesn’t show color is that Excel functions – native and user-defined – don’t change the format of a cell. TEXT may seem like it’s breaking that rule, but it just returns a string that mimics the specified formatting.

So, adding color without VBA became my ~~excuse~~ reason for creating this tool. To do so, I used conditional formatting and an additional 29 columns of formulas. You can check them out by scrolling right in the workbook above. (You can also download it by using the button at the bottom of the workbook.)

There are two general types of custom formats, so my first task was to determine which kind is being used. The first has four different conditions in the form:

Positive number formats; negative number formats; zero formats; text formats

You can use less than four conditions. If you use only one, negative numbers and zero will use the positive format. If you use two, then zeros get the positive format.

The second type of custom form is more free-form and allows you to enter two custom conditions, and corresponding formats, along with a third format that covers all conditions not met by the first two. The conditions are specified using comparison operators enclosed in brackets like “[=]” “[<>]” “[>]”, etc. The form is:

1st condition and formats; 2nd condition and formats; formats for unmet conditions

Rows 4 through 6 above have some examples. Keep in mind that these custom conditions are evaluated from left to right, and when one is met the evaluation stops, like a VBA If statement.

This Microsoft page has a good explanation of the conditional formats including both of these types.

Determining which of the two types is being is used pretty easy. If the format contains any comparison operators inside of brackets then “Has custom conditions” in column H is true. I used an array formula that searches the format for one of the operators listed in D15:D20:

{=IF($F2,SUM((ISERROR(SEARCH(main!$D$15:$D$20,main!A2))=FALSE)*1)>0,"")}

The rest of the 29 columns contain equally convoluted formulas that tease out the various conditions and whether they have associated colors. These are summarized in columns J:M and N:Q.

The conditional formatting uses COUNTIF. COUNTIF is the only function I know that understands comparison operators combined with numbers in a string. For example, if you have the numbers 1 to 10 in cells A1:A10 and “>5” in B1 you can do this:

So I ended up with a conditional format for each possible color, like this:

=INDEX(N2:Q2,MATCH(1,COUNTIF(B2,J2:M2),0))="[Red]"

It’s an array formula, but for some reason in conditional formatting you don’t have to enter them with Shft-Ctrl-Enter and you don’t get the curly brackets. If anybody knows how conditional formatting recognizes array formulas, I’d like to hear.

I know of two things that don’t work correctly in this tool:

Text is sometimes colored when it shouldn’t be. If a text color isn’t explicitly implied it should not be changed, unless the general format is used for the positive condition. With this tool it uses the positive condition format in all cases.
If you put a color or custom condition, things that are enclosed in brackets, inside of quotes, this will still recognize them as colors or conditions. So don’t do that, at least for testing.

I could fix the first, but I’m not sure about the second. If you see any other failings, feel free to leave a comment.

I mentioned that this all could be done with one line of code. Assuming you have a number in A1 and format in B1, this line of code will apply the format to the number. Note that you need to format B1 as text so you can enter formats like 0000 – without quotes – and not have Excel convert them to a single zero.

Range("A1").NumberFormat = Range("B1")

I learned a ton from doing this, and now have a much more detailed understanding of custom formats. Have you done any projects that were less-than-practical, but rewarding?

User-Friendly Survey Without VBA

Posted on August 7, 2012 by Doug Glancy

A few times a year I email workbooks containing surveys to people at about 80 schools. The overall process goes something like: I get a list of a couple of thousand records, which is then split into multiple per-school survey workbooks, which are then emailed to the schools. School staff complete the surveys and email them back. They are then merged back into one file and analyzed. Until we figure out some kind of web-based SharePoint-type system, this all works pretty well. I’ve mostly automated the emailing – adjusting subject lines, recipient lists, attachments and body on a per-school basis – and the splitting and re-merging is all push-button, so it’s a pretty efficient process.

The workbook/surveys are designed to be as user-friendly as possible, both for the people completing them and for the those analyzing the results. I use a combination of data validation and conditional formatting to guide the recipients. Ideally this might also include some VBA for things the data validation can’t handle, but it’s not worth confusion and maintenance issues that would result. So instead the workbooks contain additional conditional formatting that warns people when their data entry has gone astray. It also uses a concept I think of as conditional named ranges to provide appropriate data validation choices.

In the above picture, I’ve imagined some kind of International Pie Lovers Association, with a survey for their annual dinner. The meal choices are simple (Yes/No and Veggie/Meat) but they can choose up to three slices of pie, with a separate data validation dropdown for each slice. Yellow cells indicate where choices need to be entered. Orange indicates an error: cells that were entered when a condition called for it, but where that condition is no longer true, for example, under pie slices “3” was entered originally and “Banana Cream” was chosen, but then the number of slices was reduced to “2.”

In order to make data entry more user-friendly, I came up with data validation which points at a named range that resolves to one or more cells if a condition is true, and to nothing if it’s not. In the picture below, the condition is true in the first cell in the “Pie 1” column and so a list of pies is available, but if you clicked the dropdown in the “Pie 2” column there would be no choices. That’s because the user chose 1 in the “Pie Slices” column, so the conditional range equates to nothing for the “Pie 2” column.

There are many ways to do condtional data validation and Debra Dalgleish has lots of great info on her website. In the dark days before I had my own blog she was kind enough to post about this particular conditional range concept.

To set up the conditional range, I first create a named range called “rngPies” that points at a static list of pies in column M. Then I create the conditional range, called “rngValPie,” which points at rngPies if the condition is met, and points at nothing if it’s not. The formula for rngValPie (with I2 selected) is:

=IF(Sheet1!$H2+1>=COLUMNS(Sheet1!$H2:I2),rngPies,)

In English it says “If the number of slices selected is less than or equal to the number in this column, use rngPies, otherwise use nothing.” Here it is in the indispensable Name Manager.

The data validation then points at rngValPie. If a pie should be chosen the data validation shows the list, otherwise there’s no choices available.

Note that when you enter a conditional range in the Data Validation dialog, the condition needs to be true, otherwise you’ll get an error message. For instance, if I try to enter the data validation while J2 is the active cell, I’ll see this:

Turning the cells orange if unneeded data is entered is accomplished with conditional formatting and some helper columns to the left of the data entry area.

The helper columns contain formulas that feed the conditional formatting. I could put the formulas from the helper columns directly in the conditional formatting, but do it this way because it’s easier for us back at the office to spot invalid data by filtering the helper columns.

This system works well for us and for the folks at the schools (at least as far as I can tell). The amount of user-helpfulness is in good balance with the ease of maintenance. If you’d like to look at a sample workbook that’s Excel 2003-2010 compatible, here’s the zipped file.

Note: for another use of conditional ranges, which has worked very well for me, see this Jan Karel Pieterse post on Daily Dose of Excel.

Re-Apply Pivot Table Conditional Formatting

Posted on February 4, 2012 by Doug Glancy

I often use conditional formatting in pivot tables, often to add banding to detail rows and highlights to total rows. I like conditional formatting in XL 2010 for the most part, but sometimes it’s persnickety. It seems to change its mind from day-to-day about what’s allowed.

One well-known problem is that if you apply conditional formatting to both your row fields and the data items, like this:

and then refresh it, the formatting is wiped from the data (values) area, as shown below:

There are a couple of ways to fix this. One is to specifically apply the formats to the values area(s), a new feature as of Excel 2007. Conditional formats added this way aren’t cleared by pivot table refreshes:

This works fairly well as long as your data area only includes one values field, but if you are pivoting on multiple values fields, you’ll have to add the rule for each one. And you can’t specify row fields in this dialog, so you’ll have define the formats again for those areas. And if you alter the formats you’ll have to do it all again.

For these reasons I’d rather just apply the conditional formatting to the row headings and the values area in one fell swoop. But I don’t want to visit the condtional formatting dialog to re-expand the range each time a pivot table is refreshed.

So, I wrote the code below to expand the condtional formatting from the first row label cell into all the row label and data area cells:

Sub Extend_Pivot_CF_To_Data_Area()
Dim pvtTable As Excel.PivotTable
Dim rngTarget As Excel.Range
Dim rngSource As Excel.Range
Dim i As Long

'check for inapplicable situations
If ActiveSheet Is Nothing Then
MsgBox ("No active worksheet.")
Exit Sub
End If
On Error Resume Next
Set pvtTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
If Err.Number = 1004 Then
MsgBox "The cursor needs to be in a pivot table"
Exit Sub
End If
On Error GoTo 0

With pvtTable
'format conditions will be applied to row headers and values areas
Set rngTarget = Intersect(.DataBodyRange.EntireRow, .TableRange1)
'set the format condition's source to the first cell in the row area
Set rngSource = rngTarget.Cells(1)
With rngSource.FormatConditions
For i = 1 To .Count
'reset each format condition's range to row header and values areas
.Item(i).ModifyAppliesToRange rngTarget
Next i
End With

'display isn't always refreshed otherwise
Application.ScreenUpdating = True
End With
End Sub

The key to this code is the ModifyAppliesToRange method of each FormatCondtion. This code identifies the first cell of the row label range and loops through each format condition in that cell and re-applies it to the range formed by the intersection of the row label range and the values range, i.e., the banded area in the first image above.

This method relies on all the conditional formatting you want to re-apply being in that first row labels cell. In cases where the conditional formatting might not apply to the leftmost row label, I’ve still applied it to that column, but modified the condition to check which column it’s in.

This function can be modified and called from a SheetPivotTableUpdate event, so when users or code updates a pivot table it re-applies automatically. I’ve also added this macro to the Pivot Table Context Menu and some days it gets used a lot.