Conditional Formatting of Excel Line and XY Chart Lines
I’ve written a lot about conditional formatting of charts. One topic I have not covered is formatting of lines in line charts and XY charts, because this isn’t as easy to do.
Formula-Based Conditional Formatting
In Conditional Formatting of Excel Charts and Conditional XY Charts Without VBA, I showed examples of a formula-based technique to apply conditional formatting in Excel Charts. Essentially there are as many series in the chart as there are distinct formats you want to show. There is one point (i.e., one bar or marker) displayed where a point should be displayed, and the formulas control which series displays each point, with which format. Using formulas is more reliable than using VBA, because updating is dynamic without needing to run code whenever the data changes, and it’s easier to maintain. This approach can be applied to bar or column charts…
… to markers in line charts…
… and to markers in XY charts…
This conditional formatting is applied to each individual point, but unfortunately it cannot be applied in this way to the individual line segments connecting markers in a line chart.
VBA-Controlled Conditional Formatting
Sometimes you don’t have the luxury of using formulas for your conditional formatting. Here are a few articles showing VBA techniques for applying conditional formats to a chart:
- VBA Conditional Formatting of Charts by Series Name
- VBA Conditional Formatting of Charts by Value
- VBA Conditional Formatting of Charts by Category Label
- Pivot Table Conditional Formatting with VBA
- VBA Conditional Formatting of Charts by Value and Label
VBA-Controlled Conditional Formatting of Line Chart Lines
You might want to format lines differently if they represent rising or falling values. Green line segments for increasing trends or red for decreasing. Actually, a more color-vision-friendly combination is blue for increasing and orange for decreasing. The chart at the top of this article is a good example.
Unfortunately, line segments in a line or XY chart cannot be turned on or off using formulas, the way markers or columns and bars can be. So we’ll have to use VBA to format them.
For this tutorial. let’s dig out some web site statistics. This chart of weekly visits show some week-to-week fluctuations, with occasional flat segments which we will not want to color.
I decided to record a macro so I can figure out the syntax. The help files are pretty sketchy about this.
I started with a boring dummy chart with three series.
I turned on the macro recorder while I applied the following formatting to the dummy chart. I changed line colors, and I didn’t change the marker color but shrunk the marker size
Here’s the recorded macro. It’s no uglier than any other recorded macro.
Sub RecordedMacro1() ' ' RecordedMacro1 Macro ' ' ActiveChart.SeriesCollection(1).Select With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent5 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0.400000006 .Transparency = 0 End With With Selection.Format.Line .Visible = msoTrue .Weight = 2.25 End With Selection.MarkerSize = 4 ActiveChart.SeriesCollection(2).Select With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent6 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0.400000006 .Transparency = 0 End With With Selection.Format.Line .Visible = msoTrue .Weight = 2.25 End With Selection.MarkerSize = 4 ActiveChart.SeriesCollection(3).Select With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorBackground1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = -0.349999994 .Transparency = 0 End With With Selection.Format.Line .Visible = msoTrue .Weight = 2.25 End With Selection.MarkerSize = 4 End Sub
Well, yeah, it is kind of ugly. It selects everything, then works on the selection object, and includes all that default stuff I never touched.
Long story short (long code short), I used constants for the formatting settings, streamlined the code. I also did some magic to compare whether adjacent points showed increasing or decreasing values to decide what colors to use.
Sub ColorLinesBasedOnSlope_ThemeColor() Dim srs As Series Dim iPoint As Long Dim vValues As Variant Const thmclrBlue As Long = msoThemeColorAccent5 Const briteBlue As Double = 0.4 Const thmclrOrange As Long = msoThemeColorAccent6 Const briteOrange As Double = 0.4 Const thmclrGray As Long = msoThemeColorBackground1 Const briteGray As Double = -0.4 Const dWeight As Double = 2.25 Const lMarkerSize As Long = 4 Set srs = ActiveChart.SeriesCollection(1) vValues = srs.Values For iPoint = 2 To UBound(vValues) With srs.Points(iPoint).Format.Line.ForeColor Select Case vValues(iPoint) - vValues(iPoint - 1) Case Is > 0 .ObjectThemeColor = thmclrBlue .Brightness = briteBlue Case Is < 0 .ObjectThemeColor = thmclrOrange .Brightness = briteOrange Case Else .ObjectThemeColor = thmclrGray .Brightness = briteGray End Select End With srs.Points(iPoint).Format.Line.Weight = dWeight srs.Points(iPoint).MarkerSize = lMarkerSize Next End Sub
I selected my chart (actually, I was smart and selected a duplicate chart) and ran this code. Here’s what happened:
Oof! Not only did the code format the line segments, it also formatted the marker lines. I never did that while recording the macro.
Turns out, the object model for Excel 2007/2010 charts does not distinguish between the connecting lines and the lines that draw the markers. Well, that’s no good.
Fortunately I still can use the old tired and true Excel 2003 VBA syntax. Instead of .Format.Line.Whatever, I reverted to .Border.Whatever, and I took out that weird ThemeColor drivel, and replaced it with the familiar RGB color definitions. I present the updated code.
Sub ColorLinesBasedOnSlope_RGBColor() Dim srs As Series Dim iPoint As Long Dim vValues As Variant Const rgbMyBlue As Long = 14536083 Const rgbMyOrange As Long = 9486586 Const rgbMyGray As Long = 10921638 Const lWeight As Long = xlThick Const lMarkerSize As Long = 4 Set srs = ActiveChart.SeriesCollection(1) vValues = srs.Values For iPoint = 2 To UBound(vValues) With srs.Points(iPoint).Border Select Case vValues(iPoint) - vValues(iPoint - 1) Case Is > 0 .Color = rgbMyBlue Case Is < 0 .Color = rgbMyOrange Case Else .Color = rgbMyGray End Select .Weight = lWeight End With srs.Points(iPoint).MarkerSize = lMarkerSize Next End Sub
I selected another copy of the chart, and ran this new procedure.
Nice. It colored the lines orange and blue, except for a couple horizontal segments that were colored gray. It also kept the markers gray, but shrunk them so they don’t overwhelm the lines.
The markers aren’t really necessary. I like them, so I know for sure where there is actual data, but you can judge where the lines change their angle or color to know this. I made yet another copy of my original chart, removed the markers, and ran the formatting procedures.
Which procedure did I use? Doesn’t matter. Both produced the same result, since there were no markers for the newfangled code to mess up.