Chapter 16
Data visualizations and conditional formatting
In this chapter, you will:
Use VBA methods and properties for data visualizations
Add data bars to a range
Add color scales to a range
Add icon sets to a range
Use visualization tricks
Use other conditional formatting methods
Data visualization tools were introduced in Excel 2007 and improved in Excel 2010. Data visualizations appear on a drawing layer that can hold icon sets, data bars, color scales, and sparklines. Unlike with SmartArt graphics, Microsoft exposed the entire object model for the data visualization tools, so you can use VBA to add data visualizations to your reports.
Note
See Chapter 17, “Dashboarding with sparklines in Excel,” for more information about sparklines.
Excel provides a variety of data visualizations, as described here and shown in Figure 16-1:
Data bars—A data bar adds an in-cell bar chart to each cell in a range. The largest numbers have the longest bars, and the smallest numbers have the shortest bars. You can control the bar color as well as the values that should receive the shortest and longest bars. Data bars can be solid or a gradient. The gradient bars can have borders.
Color scales—Excel applies a color to each cell from among a two- or three-color gradient. The two-color gradients are best for reports that are presented in monochrome. The three-color gradients require a presentation in color but can represent a report in a traditional traffic light color combination of red–yellow–green. You can control the points along the continuum where each color begins, and you can choose the two or three colors.
Icon sets—Excel assigns an icon to each number. Icon sets can contain three icons, such as the red–yellow–green traffic lights, four icons, or five icons (as with cellphone signal bars). With icon sets, you can control the numeric limits for each icon, reverse the order of the icons, or choose to show only the icons.
Above/below average—These rules, which are under the Top/Bottom Rules flyout menu, make it easy to highlight all the cells that are above or below average. You can choose the formatting to apply to the cells. Note in column G of Figure 16-1 that only 30% of the cells are above average. Contrast this with the top 50% in column K.
Duplicate values—Excel highlights any values that are repeated within a data set. Because the Delete Duplicates command on the Data tab of the ribbon is so destructive, you might prefer to highlight the duplicates and then intelligently decide which records to delete. This also can be used to highlight values that appear only once in the data. Microsoft refers to this as “Unique Values,” although I disagree with that term. I would prefer an option that highlights the values that would be left after applying Remove Duplicates. If the word “Apple” appears twice in a column, neither cell will be marked as a unique value.
Top/bottom rules—Excel highlights the top or bottom n percent of cells or highlights the top or bottom n cells in a range.
Highlight cells—The legacy conditional formatting rules such as greater than, less than, between, and text that contains are still available in Excel. The powerful
Formulaconditions are also available, although you might need to use these less frequently now that you have the average and top/bottom rules.
FIGURE 16-1 Visualizations such as data bars, color scales, icon sets, and top/bottom rules are controlled in the Excel user interface from the Conditional Formatting drop-down menu on the Home tab of the ribbon.
VBA methods and properties for data visualizations
All the data visualization settings are managed in VBA with the FormatConditions collection. Conditional formatting has been in Excel since Excel 97. In Excel 2007, Microsoft expanded the FormatConditions object to handle the new visualizations. Whereas legacy versions of Excel would use the FormatConditions.Add method, Excel today offers additional methods, such as AddDataBar, AddIconSetCondition, AddColorScale, AddTop10, AddAboveAverage, and AddUniqueValues.
You can apply several different conditional formatting conditions to the same range. For example, you can apply a two-color color scale, an icon set, and a data bar to the same range. Excel includes a Priority property to specify which conditions should be calculated first. Methods such as SetFirstPriority and SetLastPriority ensure that a new format condition is executed before or after all others.
The stopIfTrue property works in conjunction with the Priority property. Say that you are highlighting duplicates but want to check only text cells. Create a new formula-based condition that uses =ISNUMBER() to find numeric values. Give the ISNUMBER condition a higher priority and apply StopIfTrue to prevent Excel from ever reaching the duplicates condition for numeric cells.
Beginning with Excel 2007, the Type property was expanded dramatically. This property was formerly a toggle between CellValue and Expression, but 13 new types were added in Excel 2007. Table 16-1 shows the valid values for the Type property. Items 3 and above were introduced in Excel 2007. The Excel team must have had plans for more conditions; items 7, 14, and 15 do not exist, so they must have been on the drawing board at one time but then removed from the final version of Excel 2007. One of these was likely the ill-fated “highlight entire table row” feature that was in the Excel 2007 beta but removed in the final version.
TABLE 16-1 Valid types for a format condition
Value | Description | VBA Constant |
|---|---|---|
| Cell value |
|
| Expression |
|
| Color scale |
|
| Data bar |
|
| Top 10 values |
|
| Icon set |
|
| Unique values |
|
| Text string |
|
| Blanks condition |
|
| Time period |
|
| Above average condition |
|
| No blanks condition |
|
| Errors condition |
|
| No errors condition |
|
Adding data bars to a range
The Data Bar command adds an in-cell bar chart to each cell in a range. Many charting experts complained to Microsoft about problems in the Excel 2007 data bars. For this reason, Microsoft changed the data bars in Excel 2010 to address these problems.
In Figure 16-2, the next-to-last cell in the left column reflects changes introduced in Excel 2010. Notice that this cell, which has a value of 0, has no data bar at all. In Excel 2007, the smallest value receives a 4-pixel data bar, even if that smallest value is 0. In addition, in Excel today, the largest bar in the data set typically takes up the entire width of the cell.
FIGURE 16-2 Excel offers many variations on data bars.
In Excel 2007, the data bars would end in a gradient that made it difficult to tell where the bar ended. Versions from Excel 2010 and newer offer a border around the bar. You can choose to change the color of the border or even to remove the border, as shown in the right column of Figure 16-2.
Excel 2010 and newer also offer support for negative data bars, as shown in the middle column of Figure 16-2; the data bars run right to left for negative values. These allow comparative histograms.
To add a data bar, you apply the FormatConditions.AddDataBar method to a range that contains your numbers. This method requires no arguments, and it returns an object of the DataBar type.
After you add the data bar, you will most likely need to change some of its properties. One method of referring to the data bar is to assume that the recently added data bar is the last item in the collection of format conditions. This code would add a data bar, identify the data bar by counting the conditions, and then change the color:
Range("A2:A11").FormatConditions.AddDatabar
ThisCond = Range("A2:A11").FormatConditions.Count
With Range("A2:A11").FormatConditions(ThisCond).BarColor
.Color = RGB(255, 0, 0) ' Red
.TintAndShade = -0.5 ' Darker than normal
End WithA safer way to go is to define an object variable of type DataBar. You can then assign the newly created data bar to the variable:
Dim DB As Databar
' Add the data bars
Set DB = Range("A2:A11").FormatConditions.AddDatabar
' Use a red that is 25% darker
With DB.BarColor
.Color = RGB(255, 0, 0)
.TintAndShade = -0.5
End WithWhen specifying colors for the data bar or the border, you should use the RGB function to assign a color. You can modify the color by making it darker or lighter, using the TintAndShade property. Valid values are from -1 to 1. Negative values make the color darker, a value of 0 means no modification, and positive values make the color lighter.
By default, Excel assigns the shortest data bar to the minimum value and the longest data bar to the maximum value. If you want to override the defaults, use the Modify method for either the MinPoint or MaxPoint properties. Specify a type from those shown in Table 16-2. Types 0, 3, 4, and 5 require a value. Table 16-2 shows valid types.
TABLE 16-2 MinPoint and MaxPoint types
Value | Description | VBA Constant |
|---|---|---|
| Number is used. |
|
| Lowest value from the list of values. |
|
| Highest value from the list of values. |
|
| Percentage is used. |
|
| Formula is used. |
|
| Percentile is used. |
|
| No conditional value. |
|
Use the following code to have the shortest bar assigned to values of 0 and below:
DB.MinPoint.Modify _ Newtype:=xlConditionValueNumber, NewValue:=0
To give the top 20% of the bars the longest bar, use this code:
DB.MaxPoint.Modify _ Newtype:=xlConditionValuePercent, NewValue:=80
An interesting alternative is to show only the data bars and not the value. To do this, use this code:
DB.ShowValue = False
To show negative data bars in Excel, use this line:
DB.AxisPosition = xlDataBarAxisAutomatic
When you allow negative data bars, you can specify an axis color, a negative bar color, and a negative bar border color. The following code shows samples of how to change the various colors. Figure 16-3 shows the data bars in column C:
Sub DataBar2()
' Add a Data bar
' Include negative data bars
' Control the min and max point
'
Dim DB As Databar
With Range("C4:C11")
.FormatConditions.Delete
' Add the data bars
Set DB = .FormatConditions.AddDatabar()
End With
' Set the lower limit
DB.MinPoint.Modify newtype:=xlConditionFormula, NewValue:="-600"
DB.MaxPoint.Modify newtype:=xlConditionValueFormula, NewValue:="600"
' Change the data bar to Green
With DB.BarColor
.Color = RGB(0, 255, 0)
.TintAndShade = -0.15
End With
With DB
' Use a gradient
.BarFillType = xlDataBarFillGradient
' Left to Right for direction of bars
.Direction = xlLTR
' Assign a different color to negative bars
.NegativeBarFormat.ColorType = xlDataBarColor
' Use a border around the bars
.BarBorder.Type = xlDataBarBorderSolid
' Assign a different border color to negative
.NegativeBarFormat.BorderColorType = xlDataBarSameAsPositive
' All borders are solid black
With .BarBorder.Color
.Color = RGB(0, 0, 0)
End With
' Axis where it naturally would fall, in black
.AxisPosition = xlDataBarAxisAutomatic
With .AxisColor
.Color = 0
.TintAndShade = 0
End With
' Negative bars in red
With .NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
' Negative borders in red
End With
End SubIn Excel, you have a choice of showing a gradient or a solid bar. To show a solid bar, use the following:
DB.BarFillType = xlDataBarFillSolid
The following code sample produces the solid bars shown in column E in Figure 16-3:
Sub DataBar3()
' Add a Data bar
' Show solid bars
' Allow negative bars
' hide the numbers, show only the data bars
'
Dim DB As Databar
With Range("E4:E11")
.FormatConditions.Delete
' Add the data bars
Set DB = .FormatConditions.AddDatabar()
End With
With DB.BarColor
.Color = RGB(0, 0, 255)
.TintAndShade = 0.1
End With
' Hide the numbers
DB.ShowValue = False
DB.BarFillType = xlDataBarFillSolid
DB.NegativeBarFormat.ColorType = xlDataBarColor
With DB.NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
' Allow negatives
DB.AxisPosition = xlDataBarAxisAutomatic
' Negative border color is different
DB.NegativeBarFormat.BorderColorType = xlDataBarColor
With DB.NegativeBarFormat.BorderColor
.Color = RGB(127, 127, 0)
.TintAndShade = 0
End With
End SubTo allow the bars to go right to left, use this code:
DB.Direction = xlRTL ' Right to Left
FIGURE 16-3 Data bars created by the macros in this section.
Adding color scales to a range
You can add color scales in either two-color or three-color scale varieties. Figure 16-4 shows the available settings in the Excel user interface for a color scale using three colors.
FIGURE 16-4 Color scales enable you to show hot spots in your data set.
As with data bars, you apply a color scale to a range object by using the AddColorScale method. You should specify a ColorScaleType of either 2 or 3 as the only argument of the AddColorScale method.
Next, you can indicate a color and tint for both or all three of the color scale criteria. Using the values shown previously in Table 16-2, you can also specify whether the shade is applied to the lowest value, the highest value, a particular value, or a percentage or at a percentile.
The following code generates a three-color color scale in the range A1:A10:
Sub Add3ColorScale()
Dim CS As ColorScale
With Range("A1:A10")
.FormatConditions.Delete
' Add the Color Scale as a 3-color scale
Set CS = .FormatConditions.AddColorScale(ColorScaleType:=3)
End With
'' Format the first color as light red
With CS.ColorScaleCriteria(1)
.Type = xlConditionValuePercent
.Value = 30
.FormatColor.Color = RGB(255, 0, 0)
.FormatColor.TintAndShade = 0.25
End With
'' Format the second color as green at 50%
With CS.ColorScaleCriteria(2)
.Type = xlConditionValuePercent
.Value = 50
.FormatColor.Color = RGB(0, 255, 0)
.FormatColor.TintAndShade = 0
End With
'' Format the third color as dark blue
With CS.ColorScaleCriteria(3)
.Type = xlConditionValuePercent
.Value = 80
.FormatColor.Color = RGB(0, 0, 255)
.FormatColor.TintAndShade = -0.25
End With
End SubAdding icon sets to a range
Icon sets in Excel come with three, four, or five different icons in the set. Figure 16-5 shows the settings for an icon set with five different icons.
To add an icon set to a range, use the AddIconSet method. No arguments are required. You can adjust three properties that apply to the icon set, and you can use several additional lines of code to specify the icon set in use and the limits for each icon.
FIGURE 16-5 With additional icons, the complexity of the code increases.
Specifying an icon set
After adding an icon set, you can control whether the icon order is reversed and whether Excel shows only the icons, and you can also specify 1 of the 20 built-in icon sets, like this:
Dim ICS As IconSetCondition
With Range("A1:C10")
.FormatConditions.Delete
Set ICS = .FormatConditions.AddIconSetCondition()
End With
' Global settings for the icon set
With ICS
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl5CRV)
End WithTable 16-3 shows the complete list of icon sets.
TABLE 16-3 Available icon sets and their VBA constants
Icon Set | Value | Description | Constant |
|---|---|---|---|
|
| 3 arrows |
|
|
| 3 arrows gray |
|
|
| 3 flags |
|
|
| 3 traffic lights 1 |
|
|
| 3 traffic lights 2 |
|
|
| 3 signs |
|
|
| 3 symbols |
|
|
| 3 symbols 2 |
|
|
| 4 arrows |
|
|
| 4 arrows gray |
|
|
| 4 red to black |
|
|
| 4 power bars |
|
|
| 4 traffic lights |
|
|
| 5 arrows |
|
|
| 5 arrows gray |
|
|
| 5 power bars |
|
|
| 5 quarters |
|
|
| 3 stars |
|
|
| 3 triangles |
|
|
| 5 boxes |
|
Specifying ranges for each icon
After specifying the type of icon set, you can specify ranges for each icon within the set. By default, the first icon starts at the lowest value. You can adjust the settings for each of the additional icons in the set, as shown here:
' The first icon always starts at 0 ' Settings for the second icon – start at 50% With ICS.IconCriteria(2) .Type = xlConditionValuePercent .Value = 50 .Operator = xlGreaterEqual End With With ICS.IconCriteria(3) .Type = xlConditionValuePercent .Value = 60 .Operator = xlGreaterEqual End With With ICS.IconCriteria(4) .Type = xlConditionValuePercent .Value = 80 .Operator = xlGreaterEqual End With With ICS.IconCriteria(5) .Type = xlConditionValuePercent .Value = 90 .Operator = xlGreaterEqual End With
Valid values for the Operator property are XlGreater or xlGreaterEqual.
Caution
With VBA, it is easy to create overlapping ranges such as icon 1 from 0 to 50 and icon 2 from 30 to 90. Even though the Edit Formatting Rule dialog box prevents overlapping ranges, VBA allows them. However, keep in mind that your icon set will display unpredictably if you create invalid ranges.
Using visualization tricks
If you use an icon set or a color scale, Excel applies a color to all cells in the data set. Two tricks in this section enable you to apply an icon set to only a subset of the cells or to apply two different colors of data bars to the same range. The first trick is available in the user interface, but the second trick is available only in VBA.
Creating an icon set for a subset of a range
Sometimes, you might want to apply a red X only to the bad cells in a range. This is tricky to do in the Excel user interface.
In the user interface, follow these steps to apply a red X to values greater than or equal to 66:
Add a three-symbols icon set to the range.
Choose Home, Conditional Formatting, Manage Rules, and edit the rule. You see the default settings that appear in Figure 16-6.
Specify no cell icon for the first two groups.
Specify that the top group has a Type of Number and >=80.
Specify that the second group has a Type of Number and >66. Excel defaults the Red X group to be used for <=66 (see Figure 16-7).
The code to create this effect in VBA is straightforward. A great deal of the code makes sure that the icon set has the red X symbols on the cells with values less than or equal to 66. To hide the icons for rules 1 and 2, set the Icon property to xlIconNoCellIcon.
FIGURE 16-6 These default rules appear when you add a three-icon set.
FIGURE 16-7 Although the first two ranges have no cell icon, use the number values to force the red X to show when the value is <=66.
The code to highlight values less than or equal to 66 with a red X is shown here:
Sub TrickyFormatting()
' mark the bad cells
Dim ICS As IconSetCondition
Dim FC As FormatCondition
With Range("A1:D9")
.FormatConditions.Delete
Set ICS = .FormatConditions.AddIconSetCondition()
End With
With ICS
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With ICS.IconCriteria(1)
.Type = xlConditionValue
.Value = 80
.Operator = xlGreater
.Icon = xlIconNoCellIcon
End With
' The threshold for this icon doesn't really matter,
' but you have to make sure that it does not overlap the 3rd icon
With ICS.IconCriteria(2)
.Type = xlConditionValue
.Value = 66
.Operator = xlGreater
.Icon = xlIconNoCellIcon
End With
End SubUsing two colors of data bars in a range
This trick is particularly cool because it can be achieved only with VBA. Say that values greater than 90 are acceptable and those 90 and below indicate trouble. You would like acceptable values to have a green bar and others to have a red bar.
Using VBA, you first add the green data bars. Then, without deleting the format condition, you add red data bars.
In VBA, every format condition has a Formula property that defines whether the condition is displayed for a given cell. Therefore, the trick is to write a formula that defines when the green bars are displayed. When the formula is not True, the red bars are allowed to show through.
In Figure 16-8, the effect is applied to the range A1:D10. You need to write the formula in A1 style, as if it applies to the top-left corner of the selection. The formula needs to evaluate to True or False. Excel automatically copies the formula to all the cells in the range. The formula for this condition is =A1>90.
Note
The formula is evaluated relative to the current cell pointer location. Even though it is not usually necessary to select cells before adding a FormatCondition, in this case, selecting the range ensures that the formula will work.
FIGURE 16-8 The dark bars are red, and the lighter bars are green. VBA was used to create two overlapping data bars, and then the Formula property hid the top bars for cells 90 and below.
The following code creates the two-color data bars:
Sub AddTwoDataBars()
' passing values in green, failing in red
Dim DB As Databar
Dim DB2 As Databar
With Range("A1:D10")
.FormatConditions.Delete
' Add a Light Green Data Bar
Set DB = .FormatConditions.AddDatabar()
DB.BarColor.Color = RGB(0, 255, 0)
DB.BarColor.TintAndShade = 0.25
' Add a Red Data Bar
Set DB2 = .FormatConditions.AddDatabar()
DB2.BarColor.Color = RGB(255, 0, 0)
' Make the green bars only
.Select ' Required to make the next line work
.FormatConditions(1).Formula = "=A1>90"
DB.Formula = "=IF(A1>90,True,False)"
DB.MinPoint.Modify newtype:=xlConditionFormula, NewValue:="60"
DB.MaxPoint.Modify newtype:=xlConditionValueFormula, _
NewValue:="100"
DB2.MinPoint.Modify newtype:=xlConditionFormula, NewValue:="60"
DB2.MaxPoint.Modify newtype:=xlConditionValueFormula, _
NewValue:="100"
End With
End SubThe Formula property works for all the conditional formats, which means you could potentially create some obnoxious combinations of data visualizations. In Figure 16-9, five different icon sets are combined in a single range. No one will be able to figure out whether a red flag is worse than a gray down arrow. Even so, this ability opens interesting combinations for those with a little creativity.
FIGURE 16-9 VBA created this mixture of five different icon sets in a single range. The Formula property in VBA is the key to combining icon sets.
Use the following code to create the crazy icon set shown in Figure 16-9:
Sub AddCrazyIcons()
With Range("A1:C10")
.Select ' The .Formula lines below require .Select here
.FormatConditions.Delete
' First icon set
.FormatConditions.AddIconSetCondition
.FormatConditions(1).IconSet = ActiveWorkbook.IconSets(xl3Flags)
.FormatConditions(1).Formula = "=IF(A1<5,TRUE,FALSE)"
' Next icon set
.FormatConditions.AddIconSetCondition
.FormatConditions(2).IconSet = _
ActiveWorkbook.IconSets(xl3ArrowsGray)
.FormatConditions(2).Formula = "=IF(A1<12,TRUE,FALSE)"
' Next icon set
.FormatConditions.AddIconSetCondition
.FormatConditions(3).IconSet = _
ActiveWorkbook.IconSets(xl3Symbols2)
.FormatConditions(3).Formula = "=IF(A1<22,TRUE,FALSE)"
' Next icon set
.FormatConditions.AddIconSetCondition
.FormatConditions(4).IconSet = ActiveWorkbook.IconSets(xl4CRV)
.FormatConditions(4).Formula = "=IF(A1<27,TRUE,FALSE)"
' Next icon set
.FormatConditions.AddIconSetCondition
.FormatConditions(5).IconSet = ActiveWorkbook.IconSets(xl5CRV)
End With
End SubUsing other conditional formatting methods
Although the icon sets, data bars, and color scales get most of the attention, there are still plenty of other uses for conditional formatting.
The remaining examples in this chapter show some of the other conditional formatting rules and methods available.
Formatting cells that are above or below average
Use the AddAboveAverage method to format cells that are above or below average. After adding the conditional format, specify whether the AboveBelow property is xlAboveAverage or xlBelowAverage.
The following two macros highlight cells that are above and below average:
Sub FormatAboveAverage()
With Selection
.FormatConditions.Delete
.FormatConditions.AddAboveAverage
.FormatConditions(1).AboveBelow = xlAboveAverage
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
Sub FormatBelowAverage()
With Selection
.FormatConditions.Delete
.FormatConditions.AddAboveAverage
.FormatConditions(1).AboveBelow = xlBelowAverage
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubFormatting cells in the top 10 or bottom 5
Four of the choices on the Top/Bottom Rules flyout menu are controlled with the AddTop10 method. After you add the format condition, you need to set three properties that control how the condition is calculated:
TopBottom—Set this to eitherxlTop10ToporxlTop10Bottom.Rank—Set this to5for the top 5,6for the top 6, and so on.Percent—Set this toFalseif you want the top 10 items. Set this toTrueif you want the top 10% of the items.
The following code highlights the top or bottom cells:
Sub FormatTop10Items()
With Selection
.FormatConditions.Delete
.FormatConditions.AddTop10
.FormatConditions(1).TopBottom = xlTop10Top
.FormatConditions(1).Rank = 10
.FormatConditions(1).Percent = False
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
Sub FormatBottom5Items()
With Selection
.FormatConditions.Delete
.FormatConditions.AddTop10
.FormatConditions(1).TopBottom = xlTop10Bottom
.FormatConditions(1).Rank = 5
.FormatConditions(1).Percent = False
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
Sub FormatTop12Percent()
With Selection
.FormatConditions.Delete
.FormatConditions.AddTop10
.FormatConditions(1).TopBottom = xlTop10Top
.FormatConditions(1).Rank = 12
.FormatConditions(1).Percent = True
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubFormatting unique or duplicate cells
The Remove Duplicates command on the Data tab of the ribbon is a destructive command. Instead of using it, you might want to mark the duplicates without removing them. If so, you can use the AddUniqueValues method to mark the duplicate or unique cells. After you call this method, set the DupeUnique property to either xlUnique or xlDuplicate.
I do not really like either of these options. Choosing duplicate values marks both cells that contain the duplicate, as shown in column A in Figure 16-10. For example, both A2 and A8 are marked, when A8 is really the only duplicate value.
Choosing unique values marks only the cells that do not have duplicates, as shown in column C in Figure 16-10. This leaves several cells unmarked. For example, none of the cells containing 17 is marked.
FIGURE 16-10 The AddUniqueValues method can mark cells such as those in columns A and C. Unfortunately, it cannot mark the truly useful pattern in column E.
As any data analyst knows, the truly useful option would be to mark any values that appear once and the first instance of any values that appear multiple times. In this wishful state, Excel would mark one instance of each value. In this case, the 17 in E2 would be marked, but any subsequent cells that contain 17, such as E8, would remain unmarked.
The code to mark duplicates or unique values is shown here:
Sub FormatDuplicate()
With Selection
.FormatConditions.Delete
.FormatConditions.AddUniqueValues
.FormatConditions(1).DupeUnique = xlDuplicate
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
Sub FormatUnique()
With Selection
.FormatConditions.Delete
.FormatConditions.AddUniqueValues
.FormatConditions(1).DupeUnique = xlUnique
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
Sub HighlightFirstUnique()
With Range("E2:E16")
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTIF(E$2:E2,E2)=1"
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubFormatting cells based on their value
The value conditional formats have been around for several versions of Excel. Use the Add method with the following arguments:
Type—Because this section deals with formatting based on the cell value, the type isxlCellValue.Operator—This argument can bexlBetween,xlEqual,xlGreater,xlGreaterEqual,xlLess,xlLessEqual,xlNotBetween, orxlNotEqual.Formula1—Formula1is used with each of the operators specified to provide a numeric value.Formula2—This argument is used forxlBetweenandxlNotBetween.
The following code sample highlights cells based on their values:
Sub FormatBetween10And20()
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=10", Formula2:="=20"
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
Sub FormatLessThan15()
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=15"
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubFormatting cells that contain text
When you are trying to highlight cells that contain a certain bit of text, you use the Add method, the xlTextString type, and an operator of xlBeginsWith, xlContains, xlDoesNotContain, or xlEndsWith.
The following code highlights all cells that contain an upper- or lowercase letter A:
Sub FormatContainsA()
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlTextString, String:="A", _
TextOperator:=xlContains
' other choices: xlBeginsWith, xlDoesNotContain, xlEndsWith
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubFormatting cells that contain dates
Conditional formatting allows you to filter to a virtual date filter. The list of available date operators is a subset of the date operators available in the pivot table filters. Use the Add method, the xlTimePeriod type, and one of these DateOperator values: xlYesterday, xlToday, xlTomorrow, xlLastWeek, xlLast7Days, xlThisWeek, xlNextWeek, xlLastMonth, xlThisMonth, or xlNextMonth.
The following code highlights all dates in the past week:
Sub FormatDatesLastWeek()
With Selection
.FormatConditions.Delete
' DateOperator choices include xlYesterday, xlToday, xlTomorrow,
' xlLastWeek, xlThisWeek, xlNextWeek, xlLast7Days
' xlLastMonth, xlThisMonth, xlNextMonth,
.FormatConditions.Add Type:=xlTimePeriod, _
DateOperator:=xlLastWeek
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubFormatting cells that contain blanks or errors
Buried deep within the Excel interface are options to format cells that contain blanks, that contain errors, that do not contain blanks, or that do not contain errors. If you use the macro recorder, Excel uses the complicated xlExpression version of conditional formatting. For example, to look for a blank, Excel tests to see whether =LEN(TRIM(A1))=0. Instead, you can use any of these four self-explanatory types:
.FormatConditions.Add Type:=xlBlanksCondition .FormatConditions.Add Type:=xlErrorsCondition .FormatConditions.Add Type:=xlNoBlanksCondition .FormatConditions.Add Type:=xlNoErrorsCondition
You are not required to use any other arguments with these types.
Using a formula to determine which cells to format
The most powerful conditional format is the xlExpression type. With this type, you provide a formula for the active cell that evaluates to True or False. Make sure to write the formula with relative or absolute references so that the formula is correct when Excel copies it to the remaining cells in the selection.
An infinite number of conditions can be identified with a formula. Two popular conditions are shown here.
Highlighting the first unique occurrence of each value in a range
Say that in column A in Figure 16-11, you would like to highlight the first occurrence of each value in the column. The highlighted cells will then contain a complete list of the unique numbers found in the column.
FIGURE 16-11 A formula-based condition can mark the first unique occurrence of each value, as shown in column A, or the entire row with the largest sales, as shown in D:F.
The macro should select cells A1:A15. The formula should be written to return a True or False value for cell A1. Because Excel logically copies this formula to the entire range, you should use a careful combination of relative and absolute references.
The formula can use the COUNTIF function. Check to see how many times the range from A$1 to A1 contains the value A1. If the result is equal to 1, the condition is True, and the cell is highlighted. The first formula is =COUNTIF(A$1:A1,A1)=1. As the formula is copied down to, say A12, the formula changes to =COUNTIF(A$1:A12,A12)=1.
The following macro creates the formatting shown in column A in Figure 16-11:
Sub HighlightFirstUnique()
With Range("A1:A15")
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTIF(A$1:A1,A1)=1"
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubHighlighting the entire row for the largest sales value
Another example of a formula-based condition involves highlighting the entire row of a data set in response to a value in one column. Consider the data set in cells D2:F15 of Figure 16-11. If you want to highlight the entire row that contains the largest sale, you select cells D2:F15 and write a formula that works for cell D2: =$F2=MAX($F$2:$F$15). The code required to format the row with the largest sales value is as follows:
Sub HighlightWholeRow()
With Range("D2:F15")
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$F2=MAX($F$2:$F$15)"
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
End SubUsing the new NumberFormat property
In legacy versions of Excel, a cell that matched a conditional format could have a particular font, font color, border, or fill pattern. Since Excel 2007, you have also been able to specify a number format. This can be useful for selectively changing the number format used to display the values.
For example, you might want to display numbers greater than 999 in thousands, numbers greater than 999,999 in hundred thousands, and numbers greater than 9,999,999 in millions.
If you turn on the macro recorder and attempt to record setting the conditional format to a custom number format, the Excel VBA macro recorder actually records the action of executing an XL4 macro! You can skip the recorded code and use the NumberFormat property, as shown here:
Sub NumberFormat()
With Range("E1:G26")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=9999999"
.FormatConditions(1).NumberFormat = "$#,##0,""M"""
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=999999"
.FormatConditions(2).NumberFormat = "$#,##0.0,""M"""
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=999"
.FormatConditions(3).NumberFormat = "$#,##0,K"
End With
End SubFigure 16-12 shows the original numbers in columns A:C. The results of running the macro are shown in columns E:G. The dialog box shows the conditional format rules that are applied.
FIGURE 16-12 Since Excel 2007, conditional formats have been able to specify a specific number format.
Next steps
Professor Edward Tufte first wrote about sparklines in 2006. These graphics are essentially word-sized charts. Chapter 17 shows you how to create dashboards from tiny charts called sparklines.