Charts and graphs in Excel have hundreds of different options. This is great for creating precisely the visualization we want but can be time-consuming to apply. When we want to apply those hundreds of settings to lots of charts, it can take hours and hours of frustrating clicking. The short code snippets below will help you apply some of the most common chart options with VBA (and hopefully turn those hours into minutes).
But don’t let me fool you into thinking that using VBA is quick and easy, it’s not. But once you’ve mastered it, you’ll know the situations when VBA is the best option.
While it might be tempting to skip straight to the section you need, I recommend you read the first section in full. Understanding Excel’s Document Object Model (DOM) is essential to understand how VBA can be used with charts.
In Excel 2013, many changes were introduced to the charting engine and DOM. For example, the AddChart2 method replaced the AddChart method. As a result, some of the code presented in this post may not work with versions before Excel 2013.
Adapting the code to your requirements
It is not feasible to provide code for every scenario you might come across; there are just too many options. By applying the principles and methods in this post, you will be able to do almost anything you want with charts in Excel using VBA.
Understanding the Document Object Model
The Document Object Model (DOM) is a term which describes how things are structured. For example:
- A Workbook contains Sheets
- A Sheet contains Ranges
- A Range contains an Interior
- An Interior contains a color setting
Therefore, to change a cell color to red, we would reference this as follows:
ActiveWorkbook.Sheets("Sheet1").Range("A1").Interior.Color = RGB(255, 0, 0)
Charts are also part of the DOM and follow similar hierarchical principles. To change the height of Chart 1, on Sheet1, we could use the following.
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 1").Height = 300
Each item in the object hierarchy must be listed and separated by a period ( . ).
While the following code may look acceptable, it will not work.
ActiveWorkbook.ChartObjects("Chart 1").Height = 300
In the DOM, the ActiveWorkbook does not contain ChartObjects, so Excel cannot find Chart 1. The parent of a ChartObject is a Sheet, and the Parent of a Sheet is a Workbook. We must include the Sheet into the hierarchy for Excel to know what you want to do.
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 1").Height = 300
With this knowledge, we can refer to any element of any chart using Excel’s DOM.
Chart Objects vs. Charts vs. Chart Sheets
One of the things which makes the DOM for charts complicated is that many things exist in many places. For example, a chart can be an embedded chart of the face of a worksheet, or as a separate sheet.
- On the worksheet itself, we find, what VBA refers to as a ChartObject. Within each ChartObject is a Chart. Effectively a ChartObject is a container which holds a Chart.
- A Chart is also a stand-alone sheet; it does not have a ChartObject around it.
This may seem confusing initially, but there are good reasons for this.
To change the chart title text, we would reference the two types of chart differently:
- Chart on a worksheet:
Sheets("Sheet1").ChartObjects("Chart 1").Chart.ChartTitle.Text = "My Chart Title" - Chart sheet:
Sheets("Chart 1").ChartTitle.Text = "My Chart Title"
The sections in bold are the same, which shows that once we have got inside the Chart, the DOM is the same.
Writing code to work on either chart type
We want to write code which will work on any chart; we do this by creating a variable which holds the reference to a Chart.
Create a variable to refer to a Chart inside a ChartObject:
Dim cht As Chart Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
Create a variable to refer to a Chart which is a sheet:
Dim cht As Chart Set cht = Sheets("Chart 1")
Now we can write VBA code for a Chart sheet or a Chart inside a ChartObject by referring to the Chart using cht:
cht.ChartTitle.Text = "My Chart Title"
OK, so now we’ve established how to reference charts and briefly covered how the DOM works. It is time to look at lots of code examples.
VBA Code Examples
Reference charts on a worksheet
Active Chart
Create a Chart variable to hold the ActiveChart:
Dim cht As Chart Set cht = ActiveChart
Chart Object by name
Create a Chart variable to hold a specific chart by name.
Dim cht As Chart Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
Chart object by number
If there are multiple charts on a worksheet, they can be referenced by their number. 1 = the first chart created, 2 = the second chart created etc, etc.
Dim cht As Chart Set cht = Sheets("Sheet1").ChartObjects(1).Chart
Loop through all Chart Objects
If there are multiple ChartObjects on a page, we can loop through each:
Dim chtObj as ChartObject For Each chtObj In Sheets("Sheet1").ChartObjects 'Include the code to be applied to each ChartObjects 'refer to the Chart using chtObj.cht Next chtObj
Loop through all selected Chart Objects
If we only want to loop through the selected ChartObjects we can use the following code.
This code is tricky to apply as Excel operates differently when one chart is selected, compared to multiple charts. Therefore, as a way to apply the Chart settings, without the need to repeat a lot of code, I recommend calling another macro and passing the Chart as an argument to that macro.
Dim obj As Object 'Check if any charts have been selected If Not ActiveChart Is Nothing Then Call AnotherMacro(ActiveChart) Else For Each obj In Selection 'If more than one chart selected If TypeName(obj) = "ChartObject" Then Call AnotherMacro(obj.Chart) End If Next End If
Reference chart sheets
Active Chart
Set up a Chart variable to hold the ActiveChart:
Dim cht As Chart Set cht = ActiveChart
Note: this is the same code as when referencing the active chart on the worksheet.
Chart sheet by name
Set up a Chart variable to hold a specific chart sheet
Dim cht As Chart Set cht = Sheets("Chart 1")
Loop through all chart sheets in a workbook
The following code will loop through all the chart sheets in the active workbook.
Dim cht As Chart For Each cht In ActiveWorkbook.Charts Call AnotherMacro(cht) Next cht
Chart
Some basic chart settings are shown below:
'Change chart type - these are common examples, others do exist. cht.ChartType = xlArea cht.ChartType = xlLine cht.ChartType = xlPie cht.ChartType = xlColumnClustered cht.ChartType = xlColumnStacked cht.ChartType = xlColumnStacked100 cht.ChartType = xlArea cht.ChartType = xlAreaStacked cht.ChartType = xlBarClustered cht.ChartType = xlBarStacked cht.ChartType = xlBarStacked100 'Create an empty chart embedded on a worksheet. Set cht = Sheets("Sheet1").Shapes.AddChart2.Chart 'Select source for a chart Dim rng As Range Set rng = Sheets("Sheet1").Range("A1:B4") cht.SetSourceData Source:=rng 'Delete a ChartObject or Chart sheet If TypeName(cht.Parent) = "ChartObject" Then cht.Parent.Delete ElseIf TypeName(cht.Parent) = "Workbook" Then cht.Delete End If 'Set the size/position of a ChartObject - method 1 cht.Parent.Height = 200 cht.Parent.Width = 300 cht.Parent.Left = 20 cht.Parent.Top = 20 'Set the size/position of a ChartObject - method 2 chtObj.Height = 200 chtObj.Width = 300 chtObj.Left = 20 chtObj.Top = 20 'Change the setting to show only visible cells cht.PlotVisibleOnly = False 'Change the gap space between bars cht.ChartGroups(1).GapWidth = 50 'Change the overlap setting of bars cht.ChartGroups(1).Overlap = 75
Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.
Therefore, what most people like you need is lots of examples that you can practice. That is why the 100 Excel VBA Macros eBook exists. It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers. The book contains:
- 100 example codes to practice reading and writing macros that will embed the language into your thinking.
- An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
- Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
- Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.
Chart Axis
There are four chart axis:
- xlValue
- xlValue, xlSecondary
- xlCategory
- xlCategory, xlSecondary
These are used interchangeably in the examples below. To adapt the code to your specific requirements you need to change the value in the brackets.
'Set chart axis min and max cht.Axes(xlValue).MaximumScale = 25 cht.Axes(xlValue).MinimumScale = 10 cht.Axes(xlValue).MaximumScaleIsAuto = True cht.Axes(xlValue).MinimumScaleIsAuto = True 'Display axis cht.HasAxis(xlCategory) = True 'Hide axis cht.HasAxis(xlValue, xlSecondary) = False 'Display axis title cht.Axes(xlCategory, xlSecondary).HasTitle = True 'Hide axis title cht.Axes(xlValue).HasTitle = False 'Change axis title text cht.Axes(xlCategory).AxisTitle.Text = "My Axis Title" 'Reverse the order of a catetory axis cht.Axes(xlCategory).ReversePlotOrder = True
Gridlines
'Add gridlines cht.SetElement (msoElementPrimaryValueGridLinesMajor) cht.SetElement (msoElementPrimaryCategoryGridLinesMajor) cht.SetElement (msoElementPrimaryValueGridLinesMinorMajor) cht.SetElement (msoElementPrimaryCategoryGridLinesMinorMajor) 'Delete gridlines cht.Axes(xlValue).MajorGridlines.Delete cht.Axes(xlValue).MinorGridlines.Delete cht.Axes(xlCategory).MajorGridli.Delete cht.Axes(xlCategory).MinorGridlines.Delete 'Change colour of gridlines cht.Axes(xlValue).MajorGridlines.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'Change transparency of gridlines cht.Axes(xlValue).MajorGridlines.Format.Line.Transparency = 0.5
Chart Title
'Display chart title cht.HasTitle = True 'Hide chart title cht.HasTitle = False 'Change chart title text cht.ChartTitle.Text = "My Chart Title" 'Position the chart title cht.ChartTitle.Left = 10 cht.ChartTitle.Top = 10 'Format the chart title cht.ChartTitle.TextFrame2.TextRange.Font.Name = "Calibri" cht.ChartTitle.TextFrame2.TextRange.Font.Size = 16 cht.ChartTitle.TextFrame2.TextRange.Font.Bold = msoTrue cht.ChartTitle.TextFrame2.TextRange.Font.Bold = msoFalse cht.ChartTitle.TextFrame2.TextRange.Font.Italic = msoTrue cht.ChartTitle.TextFrame2.TextRange.Font.Italic = msoFalse
Chart Legend
'Display the legend cht.HasLegend = True 'Hide the legend cht.HasLegend = False 'Position the legend cht.Legend.Position = xlLegendPositionTop cht.Legend.Position = xlLegendPositionRight cht.Legend.Position = xlLegendPositionLeft cht.Legend.Position = xlLegendPositionCorner cht.Legend.Position = xlLegendPositionBottom 'Allow legend to overlap the chart. 'False = allow overlap, True = due not overlap cht.Legend.IncludeInLayout = False cht.Legend.IncludeInLayout = True 'Move legend to a specific point cht.Legend.Left = 20 cht.Legend.Top = 200 cht.Legend.Width = 100 cht.Legend.Height = 25
Plot Area
'Set the size and position of the PlotArea
cht.PlotArea.Left = 20
cht.PlotArea.Top = 20
cht.PlotArea.Width = 200
cht.PlotArea.Height = 150
Chart series
Set up a Series variable to hold a chart series. 1 = First chart series, 2 = Second chart series.
Dim srs As Series Set srs = cht.SeriesCollection(1)
Referencing a chart series by name
Dim srs As Series Set srs = cht.SeriesCollection("Series Name")
Loop through each chart series
For Each srs In cht.SeriesCollection 'Do something to each series 'See the codes below starting with "srs." Next srs
Change series data
'Change series source data and name srs.Values = "=Sheet1!$C$2:$C$6" srs.Name = "=""Change Series Name""" 'Add a new chart series Set srs = cht.SeriesCollection.NewSeries srs.Values = "=Sheet1!$C$2:$C$6" srs.Name = "=""New Series""" 'Set the values for the X axis when using XY Scatter srs.XValues = "=Sheet1!$D$2:$D$6"
Display data labels
'Display data labels on all points in the series srs.HasDataLabels = True 'Hide data labels on all points in the series srs.HasDataLabels = False 'Position data labels 'The label position must be a valid option for the chart type. srs.DataLabels.Position = xlLabelPositionAbove srs.DataLabels.Position = xlLabelPositionBelow srs.DataLabels.Position = xlLabelPositionLeft srs.DataLabels.Position = xlLabelPositionRight srs.DataLabels.Position = xlLabelPositionCenter srs.DataLabels.Position = xlLabelPositionInsideEnd srs.DataLabels.Position = xlLabelPositionInsideBase srs.DataLabels.Position = xlLabelPositionOutsideEnd
Series formatting
'Change fill colour srs.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) 'Change line colour srs.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'Change visibility of line srs.Format.Line.Visible = msoTrue 'Change line weight srs.Format.Line.Weight = 10 'Change line style srs.Format.Line.DashStyle = msoLineDash srs.Format.Line.DashStyle = msoLineSolid srs.Format.Line.DashStyle = msoLineSysDot srs.Format.Line.DashStyle = msoLineSysDash srs.Format.Line.DashStyle = msoLineDashDot srs.Format.Line.DashStyle = msoLineLongDash srs.Format.Line.DashStyle = msoLineLongDashDot srs.Format.Line.DashStyle = msoLineLongDashDotDot 'Changer marker type srs.MarkerStyle = xlMarkerStyleAutomatic srs.MarkerStyle = xlMarkerStyleCircle srs.MarkerStyle = xlMarkerStyleDash srs.MarkerStyle = xlMarkerStyleDiamond srs.MarkerStyle = xlMarkerStyleDot srs.MarkerStyle = xlMarkerStyleNone 'Change marker border color srs.MarkerForegroundColor = RGB(255, 0, 0) 'Change marker fill color srs.MarkerBackgroundColor = RGB(255, 0, 0) 'Change marker size srs.MarkerSize = 8
Error Bars
'Turn error bars on/off srs.HasErrorBars = True srs.HasErrorBars = False 'Change end style of error bar srs.ErrorBars.EndStyle = xlNoCap srs.ErrorBars.EndStyle = xlCap 'Change color of error bars srs.ErrorBars.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'Change thickness of error bars srs.ErrorBars.Format.Line.Weight = 5 'Error bar settings srs.ErrorBar Direction:=xlY, _ Include:=xlPlusValues, _ Type:=xlFixedValue, _ Amount:=100 'Alternatives options for the error bar settings are 'Direction:=xlX 'Include:=xlMinusValues 'Include:=xlPlusValues 'Include:=xlBoth 'Type:=xlFixedValue 'Type:=xlPercent 'Type:=xlStDev 'Type:=xlStError 'Type:=xlCustom 'Applying custom values to error bars srs.ErrorBar Direction:=xlY, _ Include:=xlPlusValues, _ Type:=xlCustom, _ Amount:="=Sheet1!$A$2:$A$7", _ MinusValues:="=Sheet1!$A$2:$A$7"
Points
Each data point on a chart series is known as a Point.
Reference a specific point
The following code will reference the first Point.
Dim srs As Series Dim pnt As Point Set srs = cht.SeriesCollection(1) Set pnt = srs.Points(1)
Loop through all points
Dim srs As Series Dim pnt As Point Set srs = cht.SeriesCollection(1) For Each pnt In srs.Points 'Do something to each point, using "pnt." Next pnt
Point Codes
Points have similar properties to Series, but it is applied to a single data point in the series rather than the whole series. See a few examples below, just to give you the idea.
'Turn on data label pnt.HasDataLabel = True 'Set the position of a data label pnt.DataLabel.Position = xlLabelPositionCenter
Bringing it all together
Just to prove how we can use these code snippets, I have created a macro to create bullet charts. This isn’t necessarily the most efficient way to write the code, but it is to demonstrate that by understanding the code above we can create a lot of charts.
The data looks like this:
The chart looks like this:
The code which achieves this is as follows:
Sub CreateBulletChart() Dim cht As Chart Dim srs As Series Dim rng As Range 'Create an empty chart Set cht = Sheets("Sheet3").Shapes.AddChart2.Chart 'Change chart title text cht.ChartTitle.Text = "Bullet Chart with VBA" 'Hide the legend cht.HasLegend = False 'Change chart type cht.ChartType = xlBarClustered 'Select source for a chart Set rng = Sheets("Sheet3").Range("A1:D4") cht.SetSourceData Source:=rng 'Reverse the order of a catetory axis cht.Axes(xlCategory).ReversePlotOrder = True 'Change the overlap setting of bars cht.ChartGroups(1).Overlap = 100 'Change the gap space between bars cht.ChartGroups(1).GapWidth = 50 'Change fill colour Set srs = cht.SeriesCollection(1) srs.Format.Fill.ForeColor.RGB = RGB(200, 200, 200) Set srs = cht.SeriesCollection(2) srs.Format.Fill.ForeColor.RGB = RGB(150, 150, 150) Set srs = cht.SeriesCollection(3) srs.Format.Fill.ForeColor.RGB = RGB(100, 100, 100) 'Add a new chart series Set srs = cht.SeriesCollection.NewSeries srs.Values = "=Sheet3!$B$7:$D$7" srs.XValues = "=Sheet3!$B$5:$D$5" srs.Name = "=""Actual""" 'Change chart type srs.ChartType = xlXYScatter 'Turn error bars on/off srs.HasErrorBars = True 'Change end style of error bar srs.ErrorBars.EndStyle = xlNoCap 'Set the error bars srs.ErrorBar Direction:=xlY, _ Include:=xlNone, _ Type:=xlErrorBarTypeCustom srs.ErrorBar Direction:=xlX, _ Include:=xlMinusValues, _ Type:=xlPercent, _ Amount:=100 'Change color of error bars srs.ErrorBars.Format.Line.ForeColor.RGB = RGB(0, 0, 0) 'Change thickness of error bars srs.ErrorBars.Format.Line.Weight = 14 'Change marker type srs.MarkerStyle = xlMarkerStyleNone 'Add a new chart series Set srs = cht.SeriesCollection.NewSeries srs.Values = "=Sheet3!$B$7:$D$7" srs.XValues = "=Sheet3!$B$6:$D$6" srs.Name = "=""Target""" 'Change chart type srs.ChartType = xlXYScatter 'Turn error bars on/off srs.HasErrorBars = True 'Change end style of error bar srs.ErrorBars.EndStyle = xlNoCap srs.ErrorBar Direction:=xlX, _ Include:=xlNone, _ Type:=xlErrorBarTypeCustom srs.ErrorBar Direction:=xlY, _ Include:=xlBoth, _ Type:=xlFixedValue, _ Amount:=0.45 'Change color of error bars srs.ErrorBars.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'Change thickness of error bars srs.ErrorBars.Format.Line.Weight = 2 'Changer marker type srs.MarkerStyle = xlMarkerStyleNone 'Set chart axis min and max cht.Axes(xlValue, xlSecondary).MaximumScale = cht.SeriesCollection(1).Points.Count cht.Axes(xlValue, xlSecondary).MinimumScale = 0 'Hide axis cht.HasAxis(xlValue, xlSecondary) = False End Sub
When to use the Macro Recorder?
The Macro Recorder is one of the most useful tools for writing VBA for Excel charts. The DOM is so vast that it can be challenging to know how to refer to a specific object, property or method. Studying the code produced by the Macro Recorder will provide the parts of the DOM which you don’t know.
As a note, the Macro Recorder creates poorly constructed code; it selects each object before manipulating it (this is what you did with the mouse after all). But this is OK for us. Once we understand the DOM, we can take just the parts of the code we need and ensure we put them into the right part of the hierarchy.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
While updating the color for graph we are getting following error
Run time error ‘1004’
parameter not valid
Dim Cht1 As Chart
Set Cht1 = ActiveSheet.ChartObjects(“Chart 1”).Chart
Cht1.ChartArea.Select
Cht1.FullSeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Cht1.ChartArea.Select
Cht1.FullSeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Cht1.ChartArea.Select
Cht1.FullSeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Cht1.FullSeriesCollection(4).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Hi Ramprakash – the VBA code above is applied to the Series 1, 2, 3 and 4, but you probably have less than 4 series in the chart.
Also, you don’t need to use the Cht1.ChartArea.Select lines, they don’t achieve anything.
Very useful!!! THANK YOU
I’m writing a comprehensive Chart Class that includes all/most of the items stated here. This was very well written/comprehensive. Thank you.
Glad to be of help… Sounds like an interesting project.
Great work!
if i have more than 1 chart/ different chart in my sheet, so what code should I use?
You can either loop through all the charts, or explicitly refer to them using their chart name.
Hi,
Thanks for sharing the knowledge. It’s very useful, but I encounter some problems. I try to change all pie charts to doughnut charts at once, but it doesn’t work. Would you mind looking at my code?
Sub ChangeCharttype()
Dim cht As Chart
Set cht = ActiveChart
For Each cht In ActiveWorkbook.Charts
cht.ChartType = xlDoughnut
Next cht
End Sub
Hi Natt
Are the charts on the worksheet or separate chart sheets?
If they are all on the worksheet then the following macro should do it:
Sub ChangeCharttype() Dim cht As ChartObject For Each cht In ActiveSheet.ChartObjects cht.Chart.ChartType = xlDoughnut Next cht End SubIf they are chart sheets then the following would work:
Sub ChangeCharttype() Dim cht As Chart For Each cht In ActiveWorkbook.Charts cht.ChartType = xlDoughnut Next cht End SubHi,
Thank you so much. I really appreciate your help.
Hi,
Quick question: I have a chart on a separate chart sheet where I would like to add a developer object (button, option button or check box) to control whether data is shown quarterly or monthly. Any idea how to do this?
Thanks!
I’ve written a post which illustrates how to achieve this:
https://exceloffthegrid.com/switch-chart-between-monthly-and-quarterly/
Hello =,
In the below code i am trying to use variables for defining the chart area.
I am trying to replicate the chart area of “Line A” using variables in “Line B”
Problem: When i run the code, Row 8 is also getting included in the Plot Area. (It is supposed to have only row 7 & 9).
Help would be much appreciated!
——————————————————————————————————————
Set Range1 = Sheets(“Error Breakdown”).Range(Cells(7, 2), Cells(7, 13 ))
Set Range2 = Sheets(“Error Breakdown”).Range(Cells(9, 2), Cells(9, 13 ))
ActiveSheet.ChartObjects(“Chart 2”).Activate
ActiveChart.PlotArea.Select
Line A: ‘ ActiveChart.SetSourceData Source:=Range(“B7:M7,B9:M9”)
Line B: ActiveChart.SetSourceData Source:=Range(Range1, Range2)
—————————————————————————————————————–
Hi Mukesh,
It’s because in Line A, Range(“B7:M7,B9:B9”) is a range based on a string. To achieve the same for Line B, you need to turn Range1 and Range2 into strings and concatenate with a comma in between.
The following should work, though I’ve not tested it.
Extremely useful and comprehensive content.
Thanks a lot. Amazing Job!
I was wondering if there is any way to format the serieses in pattern colors based on their values.
For instance: the series with highest values to be red. The middle with red.
The bottom one based on y axis to be blue.
Hi Sulaiman – yes it would be possible.
1) Loop through each chart series
2) Test to see if it is the low, mid, or highest value (probably requiring another loop)
3) Format the chart according to the result of the test from (2).
It would be quite a lot of code, but certainly possible.