Do you work with different series in your Excel charts?
Here is what I would like the final chart to loo like. Note the white dashed lines in front of the columns:
Setup:I started with the chart I ended with in my last post, Points Of Interest.
I then added some data to a separate worksheet in my workbook to replicate the horizontal gridlines.
Note in Col A that I used the exact same scale that I used for Series 1 which is what was used to plot the column charts (Really 1 chart)
Also note that I put data that is used for formatting on a separate worksheet from "Value Data". This makes it easier to maintain data as it needs to updated overtime
1. Horizontal gridlinesI am going to add fake horizontal gridlines to the chart, so first I'l remove the current horizontal gridlines.
- Click on the chart (This will activate the Chart Tools Group on the Ribbon)
- Click on "Layout" in the chart tools group
- Click on gridlines
- Click on Primary Horizontal Gridlines
- Click on "None"
2. Add "Fake" horizontal gridlinesI copied all data from the formatting tab including the horizontal axis column, clicked on my chart and pasted the new series.
I could click on each of the new series on the chart and change the chart type from column to line and apply all of the formatting. But I would like to do it with VBA. So I'll step through each part of the process separately
3. Change the chart type:First I need to chage the chart type for each series that is not a value series which was series 1. So I'll loop through the SeriesCollection starting at number 2
1: Option Explicit 2: Sub SetChartType() 3: Dim wb As Workbook 4: Dim ws As Worksheet 5: Dim i As Integer 6: Set wb = ThisWorkbook 7: Set ws = wb.Worksheets("Sheet1") 8: With ws 9: .ChartObjects(1).Activate 10: For i = 2 To .ChartObjects(1).Chart.SeriesCollection.Count 11: .ChartObjects(1).Chart.SeriesCollection(i).ChartType = xlLine 12: Next i 13: End With 14: 'Tidy up 15: Set ws = Nothing 16: Set wb = Nothing 17: End Sub
4. Format the color of the linesI would like each of the horizontal lines to be white
1: Option Explicit 2: Sub SetChartColor() 3: Dim wb As Workbook 4: Dim ws As Worksheet 5: Dim i As Integer 6: Dim lWhite As Long 7: Set wb = ThisWorkbook 8: Set ws = wb.Worksheets("Sheet1") 9: lWhite = RGB(255, 255, 255) 10: With ws 11: .ChartObjects("Chart 1").Activate 12: With ActiveChart 13: For i = 2 To .SeriesCollection.Count 14: .SeriesCollection(i).Select 15: With Selection 16: .Format.Line.ForeColor.RGB = lWhite 17: End With 18: Next i 19: End With 20: End With 21: 'Tidy up 22: Set ws = Nothing 23: Set wb = Nothing 24: End Sub
5. Change the line type:The chart is looking pretty good. But the lines are a little thick, I would like something more subdued. I played around a bit with different line weights and dash styles until I found values that I liked
1: Option Explicit 2: Sub SetLineProperties() 3: Dim wb As Workbook 4: Dim ws As Worksheet 5: Dim i As Integer 6: Dim lWhite As Long 7: Set wb = ThisWorkbook 8: Set ws = wb.Worksheets("Chart") 9: lWhite = RGB(255, 255, 255) 10: With ws 11: .ChartObjects("Chart 1").Activate 12: With ActiveChart 13: For i = 2 To .SeriesCollection.Count 14: .SeriesCollection(i).Select 15: With Selection 16: .Format.Line.Weight = 1.5 17: .Format.Line.DashStyle = msoLineRoundDot 18: End With 19: Next i 20: End With 21: End With 22: 'Tidy up 23: Set ws = Nothing 24: Set wb = Nothing 25: End Sub
I added the major horizontal gridlines back to the chart and made them very thin and light grey
The final chart:
The lines may be a little thin for your tastes, just adjust the line weight until you get the effect you are trying to achieve.
I hope you enjoy the post and that you find something of value in it.