I'm attempting to write a code that will change the colour of a series in a chart based on the name of each series. i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.
View 5 RepliesI have a stacked area chart located on its own individual worksheet. I would like to use VBA to apply colours to individual series based on the name of that series. I have applied the following code which I found on the following help forum, changing it slightly to fit my needs: http://www.ozgrid.com/forum/showthread.php?t=91381
View 3 Replies View RelatedI am using a bar graph and would like conditional formatting for a series collection. So if the series collection is greater than 30%, the single entry (not the whole series) would turn red. I have attached an image of what I currently have. There is a data validation list at the top which tells the graph what series to use, so the data is always changing.
I hope this all makes sense. Basically, if an entry is above 30%, I want the single entry in the series to turn red.
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
View 3 Replies View RelatedI have created a column chart and am now trying to change the colour of one
of the bars. Here is the code I am using:
With chtChart
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End With
I have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph. e.g. if the cell displays 80% - the chart with show 80% - simple.
however, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
it is possible to have the series of a stacked graph to change color based on the source value.
e.g.
i have a stacked cone graph made up of 5 piece (series)
i need each piece colored either green, yellow or red depending on whether its value is large than another
I have a chart in a workbook that works fine by looking at the following data series:
=Workings!$A$2:$C$35
In the data series above...where the numbers are 2 and 35 I have named ranges "CHART_Start_Row" and "CHART_End_Row" which will change dynamically as appropriate and can replace these static numbers - but how do I incorporate these named ranges into the data series reference?
I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?
With cht
.ChartType = xlColumnClustered
' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
I would like it to loop through all the charts on the "Graphs" sheet.
It works well but gives a "Run-time error '1004'" Application-defined or object-defined error afterward that I would like to get rid off.
VB:
Sub Colour_Chart_working()
Dim rngColors As Range
Dim rngCell As Range
[Code].....
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
Excel version: Excel 2010
OS: Windows 7
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 Replies View RelatedI have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
I have a problem relating to a bar chart that I just can't get around. I have Excel 2003. My problem is this. I have created an attendance record sheet for members of a club. The sheet records attendances for two separate events which occur a week apart every month. The names of the members are shown vertically in one column. Multiple parallel columns then follow in which members' attendances are recorded. At the top of the sheet, at the head of each attendance column, are two headings, one on top of the other. The first of these is the date, and the second is the attendance rate shown as a percentage. I can generate a bar chart alright from these two headings. My problem is that I want some way of showing the two different events every month as two different colours, event one in red and event two in blue, so that they will stand out. So far the chart will only let me show everything in one colour.
View 9 Replies View RelatedI'm attempting to create a dynamically built donut chart using VBA, but I'm having trouble inserting a variable to be used as one of the values.
The chart graphs 3 data points:
ASC GP - Located in cell T4
ACC GP - Located in cell AE4
ACT GP - variable called LocSumActGP
The code snippet I assumed would work looked like this:
Code:
.SeriesCollection(1).Values = "='Location Summary'!$T$4,'Location Summary'!$AE$4," & LocSumActGP
But it doesn't seem to like that.
I have the following code which puts a series onto a chart
With Charts("Test Chart")
.SetSourceData Source:=Sheets("Data").Range("A1:B" & Nreadings), PlotBy:=xlColumns
.Axes(xlCategory).MinimumScale = 1
.Axes(xlCategory).MaximumScale = Nteeth
.Axes(xlValue).MinimumScale = Miny
.Axes(xlValue).MaximumScale = Maxy
End With
I would like to add a 2nd or third series onto the same chart using the same scale etc. How do I do this in VBA code? Whenever I add another line to add a series it just overwrites the first series I have shown here.
I have calculated data (the column and row headings are calculated as well) similar to this for which I need to create an automatically updating chart.
******** language="JavaScript" ************************************************************************>Microsoft Excel - test.xlsm___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1 BalloonsFlagsCushionsPillowsStickers2Blue136013Red352104Green703245Yellow075056Orange558327White26073Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The chart should update if the data is filtered; so if a user doesn't want to see the 'orange' row they can filter the data accordingly. There is a finite number of rows and columns in the range but sometimes there will be less data. I have tried to convert the data to a table and that works quite well except the legend keys persist: so if there is no data for pillows and stickers the legends still appear and have to be removed manually. Also, there seems to be a bug in Excel 2007 whereby the removal of a chart linked to table data causes a persistent error message to appear.
I know that I need to created a named range for the data series using OFFSET, and I have also read that I need to refresh this formula using VBA but I am now very confused and none of my attempts quite work.
my problem is:
I have a whole list of data which changes according to the users preferences
It comprises of a NAME, X Value, and Y Value and the addition of X and Y. (these all have auto filters on them)
The user inputs under these fields, as many rows as they wish (which will be around 20ish)
I want it so when i click a button "CREATE GRAPH"
it selects the highest 10 values (if there is 10 values, if not then all the values) from the 4th column (X+Y) and then creates a graph. i want each row to be a new series...
I have a Scatter chart that plots a simple XYScatter plot. The data for this plot can be very wide ranging, and for that reason, I use AutoScaling to scale the axes. Nine times out of ten, it works just fine, but it seems that when the X data ranges from about 5200 to 7200 +/-, the autoscaling does not calculate well (example attached).
Autoscale range will go from 0 to 8000, leaving a large area of unplotted graph space.
I thought the original issue was that I may be plotting zeros or blanks, but I've eliminated that problem. I have yet to find another range of values that causes it, but I'm sure there's some. What is it about the way that Excel calculates its AutoScaling that I'm missing?
What do you all recommend to eliminate this problem? Manually scaling it is not really an option in this case, as I want this to be completely transparent to the user for ANY range of data.
I am trying to create a chart that will allow the user to add/remove series to compare with each other. When adding a series, Excel gives it a name (e.g Series1) the macro is then set to rename it to a particular cell, however the name Excel gives the series varies depending on the order in which the series are added.
ActiveChart.SeriesCollection(SeriesCollection.Count)
instead of
ActiveChart.SeriesCollection(1)
would work collect the last series added, which is what I want, but when I apply this I get an run-time '424' error.
I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....
View 9 Replies View RelatedI have a bar chart which compares two columns of data. the data in one column is fixed to 100. the data in the other column varies. a third column states whether each entry is "A" or "M". Is there a way to include the A or M on the bar chart? Perhaps by having a set colour whether the data is A or M?
View 1 Replies View RelatedI want to use 4 toggle buttons to be able to add and remove 4 different series to a chart. When the toggle button is clicked, a specific series is added to a chart, and when it is unclicked then the series would be removed.
The problem I am running into is that unless I add and remove each series in a specific order, I will get an error because I may be trying to remove a series whose series number is not on the series collection in the chart.
for example: if I remove series number 2 with the toggle button for that specific series, then the toggle button for series number 3 will remove the old series 4 (which is now 3), and the toggle button for series 4 won't work because series 4 will now have become series 3.
Therefore, would there be a way to remove a series based on the name of the series, rather than the series collection number?
Is there a way of determining the series that has been selected on a chart?
View 3 Replies View RelatedI have a chart with 3 series':
Series1
Series2
Series3
The chart is called chtEngagement. I want to use vb to update these series'.
Series1 should be shown as: