A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
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.
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.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide