Conditional Formatting of Lines in an Excel Line Chart Using VBA

You may have sets of data for several categories, and you want to show the values for each category, and highlight some kind of range for each category (a permissible range, a target range, or some statistically computed range).

The following chart shows three categories (A, B, and C) with highlighted ranges and several data points for each.

This combination chart is simple to make.

The Individual Val

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.

 ues

We can plot the points as an XY Scatter chart series. This chart uses 1, 2, and 3 for categories A, B, and C.

There is substantial overlap between points, so it would be helpful to jitter the points, that is, spread them out laterally so they no longer overlap.

And of course, we want to show A, B, and C instead of 1, 2, and 3. Plus add the highlighted ranges for each category.

The Data

We’ll use the following data. The floating columns are defined in B2:D5, and the actual points are described in F2:I17. Column F has the point identification, category and index, which will not be shown in the chart. Column G has X values 1, 2, and 3 corresponding to the categories A, B, and C. Column H has the jittered X values. Column I has the actual Y values.

I’ve never encountered a useful jittering algorithm, so I fudged my own, which works well enough. The formula in H3 is

=G3+0.2*(RAND()-RAND())

This gives a random number within ±0.2 of G3, biased closer to the middle of the ±0.2 range.

Building the Chart

Make a stacked column chart with the data in B2:D5.

Format the bottom series (“Min”) so its fill is No Color. The result is the floating column chart that serves as a backdrop for the actual points.

When an XY series is plotted on the axes of a column chart, an X value of 1 corresponds to the first category, 2 corresponds to the second category, etc. A value between 1 and 2 will be plotted proportionally between the first and second categories: 1.5 is midway between the columns at A and B. The jittered X values between 0.8 and 1.2 will fit within the width of the column at A.

Copy the range H2:I17, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, category labels in first column.

The new series was added as a new set of columns stacked on the floating columns and on extra categories further to the right.

Right click one of the added points, choose Change Chart Type, and select the XY variant with markers and no lines.

The XY points were initially plotted onto secondary axes, which are misaligned from the primary axes of the floating columns. Format the XY series so it is plotted on the primary axis to finish the chart.