Conditional Formatting of Lines in an Excel Line Chart Using VBA

Conditional Formatting of Excel Line and XY Chart Lines

Chart with conditionally formatted line segments

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…

Conditionally Formatted Column Chart

… to markers in line charts…

Conditionally Formatted Line Chart

… and to markers in XY charts…

Conditionally Formatted X-Y Points

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-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.

Chart with line segments to be conditionally formatted

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.

Dummy chart before recording a line formatting macro

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

Dummy chart after recording a line formatting macro

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:

Chart with conditionally formatted line segments and ugly markers

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.

Chart with conditionally formatted line segments and nice markers

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.

Chart with conditionally formatted line segments and no markers

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.

Peltier Tech Chart Utility

Comments

  1. Hi Jon. I’ve been working today on formatting lines on charts in excel using VBA.
    Encountered the same issue you described here … “Oof! Not only did the code format the line segments, it also formatted the marker lines. I never did that while recording the macro”
    I thought I was going crazy here. Because I need the marker lines to keep 1pt thick.

    I’ve got one question: It seems that the Border.Width procedure does not let you select a width value with double precision as the Format.Line.Weight procedure does.

    How can I have double precsion line widths (e.g 1, 1.2, 1.5 and 2.25 pts) set with the Border.Width procedure? Or am I stuck with xlThick or nothing?
    Thanks!
    Pato

  2. Border.Width use named constants xlHairline, xlThin, xlMedium, and xlThick. I don’t know the integer values of these constants, but it doesn’t matter. xlThin is 0.75 points, xlMedium is 1.5, and xlThick is 2.25 points. That’s all the precision the old syntax needed.

  3. THank you for the info!

  4. And If I need to conditionally format the line on the graph in red – what would be the value in this expression:
    rgbMyRed as Long = ???
    (rgbMyOrange As Long = 9486586)

  5. Igor –

    To determine the Long value if you know the RGB values, use RGB(red, green, blue), where red, green, and blue each range from 0 to 255. Of course, you can’t declare a constant using a formula for its value, but you can easily get the value you need:

    In the Immediate Window, enter this expression and press Enter:

    ?RGB(255,0,0)

    The answer is 255. So

    Const rgbMyRed As Long = 255

    Of course, if it’s definitely always going to be red and you won’t need to change it in the declarations, you can use the built-in constant vbRed, which is 255, right in your code. In fact, this also works:

    Const rgbMyRed As Long = vbRed

  6. I have this situation.
    A line chart plotting 4 sets of data – past year, forecast, latest forecast and budget. These are all populated from an array that points to a datasheet. While I am happy ho have a past year line, budget line and forecast line, I want the actual line to merge with the latest forecast so that the incomplete months show as a dotted rather than solid line value. Each time the month passes I change the cell from forecast to actual so that this should then change the line segment from dotted to solid. Can this be done using a variation of the above without VBA Coding?

  7. Callan –
    For the actual-budget dual line, check out the non-VBA approach in Category Axis Tricks for Line and Area Charts.

  8. Hi Jon,
    I have a question in addition to what you developped here. I made a custom ribbon in Excel 2007 that formats graphs exactly the way I want them to. When change a normal sized graph to a small graph I want the linesize of the series to go from 2 to 1.5 and the marker linesize from 1 to 0.5.
    I face two problems doeing this. I have difficulties in sizing the lines to decimals (1.5 becomes 1) and the second problem concerns my wish to have different linesizes for the marker and the line itself.
    This is the function I call, to execute what I just explained:

    Sub Resize_Main(TypeResize As String)

    ‘ Procedure to size and align Selected charts of a worksheet
    ‘ New Sheet is added where the resized graphs are stored
    ‘ User specifies: Number of charts across (1 to max )
    ‘ Procedure automatically freezes text size to avoid distorting text as charts resized

    Dim Chrt_width As Single
    Dim Chrt_height As Single
    Dim Font_size As Integer
    Dim Chrts_across As Long
    Dim ChrtO As ChartObject
    Dim i As Integer
    Dim MarkerResize1 As Double
    Dim MarkerResize2 As Double
    Dim MarkerLineSize As Double
    Dim LineSize As Double
    Dim sSub As String
    Dim TypeSel As String
    Dim ChartName As String
    Dim MarkerborderSize As Integer

    Dim mySeries As Series
    Dim seriesCol As SeriesCollection
    Dim J As Integer

    TypeSel = TypeName(Selection)

    sSub = “Resize_Main”

    On Error GoTo errorhandling

    ‘ Define
    If TypeResize = “Small” Then
    ‘Chrt_width = 222.8031
    Chrt_width = 205
    Chrt_height = 160
    Font_size = 6
    MarkerResize1 = 4
    MarkerResize2 = 5
    MarkerLineSize = 0.5
    LineSize = 1.5
    ElseIf TypeResize = “Big” Then
    Chrt_width = 426.792
    Chrt_height = 227.6224
    Font_size = 8
    MarkerResize1 = 5
    MarkerResize2 = 6
    MarkerLineSize = 1
    LineSize = 2
    ElseIf TypeResize = “PPT2″ Then
    Chrt_width = 290
    Chrt_height = 182.0979
    Font_size = 7
    MarkerResize1 = 3
    MarkerResize2 = 4
    MarkerLineSize = 1
    LineSize = 2
    Else
    Chrt_width = 585
    Chrt_height = 318.67136
    Font_size = 10
    MarkerResize1 = 5
    MarkerResize2 = 6
    MarkerLineSize = 1
    LineSize = 2
    End If

    Call Freeze_text ‘ Procedure to freeze text for all charts on active sheet

    ‘ Loop through all selected charts on active sheet
    If (TypeSel “ChartArea” And TypeName(Selection) “DrawingObjects”) Then
    MsgBox (“0 charts selected.”)
    Exit Sub
    ElseIf (TypeSel = “ChartArea”) Then
    If ActiveChart.ChartType = 4 Or ActiveChart.ChartType = 65 Then
    ‘SeriesResize = ResizeSeries(ActiveChart, LineResize)
    MarkerResize = ResizeMarker(ActiveChart, MarkerResize1, MarkerResize2, MarkerLineSize, LineSize)
    End If
    Selection.Width = Chrt_width
    Selection.Height = Chrt_height
    ActiveChart.ChartArea.Font.Size = Font_size
    ElseIf (TypeSel = “DrawingObjects”) Then
    For Each ChrtO In Selection
    If ChrtO.Chart.ChartType = 4 Or ChrtO.Chart.ChartType = 65 Then
    ‘SeriesResize = ResizeSeries(ChrtO.Chart, LineResize)
    MarkerResize = ResizeMarker(ChrtO.Chart, MarkerResize1, MarkerResize2, MarkerLineSize, LineSize)
    End If
    ChrtO.Width = Chrt_width
    ChrtO.Height = Chrt_height
    ChartName = ChrtO.Chart.Name
    ChrtO.Chart.ChartArea.Font.Size = Font_size
    Next ChrtO
    End If
    Exit Sub
    errorhandling:
    MsgBox “Please contact the CIC-team. An error has occured in the ” & sSub & ” sub”

    End Sub

  9. Unfortunately the Excel 2007-2013 object model does not appropriately distinguish between the marker outline size and the connecting line size. Using the new syntax, they have to be the same.

    You can use the old syntax, which allows for a marker outline of 0.75 points (1 pixel). The line can be xlhairline, xlthin, xlmedium, and xlthick. In classic Excel, these correspond to 0.25, 0.75, 1.5, and 2.25 points, but in 2007+ they come across as 0.25 (I think), 1, 2, and 3 points.

  10. Mr. Peltier

    This is a great example, thanks very much. My question is how would I edit the macro to run on multiple lines in the same chart?

    Thanks,
    Camden

  11. I actually figured out how to run on multiple lines using another for loop. Is there a way to change the case to highlight the line segments which are above a certain value?

  12. Camden –
    Modify the code as follows, to evaluate vValues(iPoint), not vValues(iPoint) - vValues(iPoint - 1). Adjust the values in the Case Is lines, and use more of these and more colors if you want.

          Select Case vValues(iPoint)
            Case Is > 10
              .Color = rgbMyBlue
            Case Is > 5
              .Color = rgbMyOrange
            Case Else
              .Color = rgbMyGray
          End Select
  13. For a non-VBA way to achieve this conditional formatting, see Different color for up and down line segments in an Excel chart

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.