Points Of Interest

Hello World! (Sorry, I could not resist J) I was recently asked by our host, Jordan, if I would be interested in being a guest author here at Option Explicit VBA. I quickly and humbly accepted. I will strive to do my best to add something of value. Let’s dive right in.

I was inspired the other day by Chandoo’s post on his blog in regards to tax burden as well as Jared’s subsequent submission regarding service levels. Both charts use a consistent color across what appears to be different series in panel charts that are arranged closely together.

In fact, they are not, the area charts are one series with blank rows or columns inserted in the data range to create the separated effect. Here is a sample initial column chart I created using the same concept

So far, so good – but I would like each “Series” to have a different color. I selected some data points and changed the fill color

Looking good, but I’ll need to manually select an additional 22 data points and change the fill color for each point. It gets worse if I want to add additional, “Series” to the chart or decide to go back and change a color – more manual work!

So I thought to myself, “Self, there must be an easier way!” The good news is that there is an easier way through VBA! Let’s cook up some code (Option Explicit VBA – Remember?)

I only have one ChartObject with one SeriesCollection, so that part is straight forward. But there are many points in the SeriesCollection to be considered. Additionally, I dont want to plot anyting or add color to anything for points 13 and 25 where I have blank rows in my data.

So, I want to do something with points 1-12, 14-25, 27-38. Sounds like a good candidate for a Select Case..Case..End Select structure.

Option Explicit
1: Sub ColorDataPoints()
2: Dim wb As Workbook
3: Dim ws As Worksheet
4: Dim i As Integer
5: Dim lBlue As Long
6: Dim lRed As Long
7: Dim lGreen As Long
8: Set wb = ThisWorkbook
9: Set ws = wb.Worksheets("Sheet2")
10: lBlue = RGB(79, 129, 189)
11: lRed = RGB(192, 0, 0)
12: lGreen = RGB(155, 187, 89)
13: With ws
14: For i = 1 To .ChartObjects(1).Chart.SeriesCollection(1).Points.Count
15: Select Case i
16: Case 1 To 12
17: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lBlue
18: Case 14 To 25
19: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lRed
20: Case 27 To 38
21: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lGreen
22: End Select
23: Next i
24: End With
25: 'Tidy up
26: Set ws = Nothing
27: Set wb = Nothing
28: End Sub

Now I have a chart with one x-axis and what appears to be 3 different series, when in fact, it is one. Perhaps more importantly, I have a process that requires very little updating as my needs change to display more “Series” or to change colors.

More on the Points Collection.

Download the workbook .

How do you work with points in your charts and VBA? Let us know in the comments section.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s