Colour Chart Data Points
May 11, 2007
How do i conditionally colour chart data points. This is an example of my data from which the chart is created from.
Salesman A 10
Salesman B 10
US Total 20
Salesman C 20
Salesman D 10
Philipines Total 30
When the chart is created, i would like the country total data points to be in orange while the rest of the data points should remain in white. I tried this code and it returned me an error.
ActiveChart.SeriesCollection("Average of Margin").Points("ID Total").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
I also tried the online F1 help it told me to use Points(Index). However i do not want to refer to the ponint with an index as the number of salesman or country could change. Therefore changing the index points.
View 4 Replies
ADVERTISEMENT
Oct 3, 2012
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].....
View 1 Replies
View Related
Nov 26, 2013
Attached is an excel file of my issue. I want to stretch the graph so it shows at least 3-4 months. at the moment is only shows 1 month.
Timeline format - Copy.xlsx
View 1 Replies
View Related
Jan 13, 2009
I have the following chart that displays average speed for my bike rides (Green line). The problem is that I don't want the line going to points with no value or zero value. So, the first month would just be the point then, when I enter in an avg speed in Feb then the line would connect to Feb. Is this possible?
View 9 Replies
View Related
Jun 3, 2014
I have a macro that runs every 15 minutes and stores data into a table. This table is attached to a chart and auto updates with the new data every 15minutes. The table will house historical data over time, however I only want the chart to reflect the last 96 entries (15min data points over 24hours). So, once i have enough data my table will be A2:A97. When A98 is populated, i want the data in A2 to be removed from the chart only.
Here is a breakdown:
A2:A97 is currently displayed on the chart.
I enter new data into A98.
The chart will now display A3:A98.
I enter new data into A99.
The chart will now display A4:A99.
I enter new data into A100.
The chart will now display A5:A100.
and so on.
How I can accomplish this? Does this need to be done in VBA?
View 3 Replies
View Related
Jan 13, 2009
I have the following chart that displays average speed for my bike rides (Green line). The problem is that I don't want the line going to points with no value or zero value. So, the first month would just be the point then, when I enter in an avg speed in Feb then the line would connect to Feb. Is this possible?
View 9 Replies
View Related
May 20, 2007
I have a time series chart ( dates on x axis , with the data values on y axis ). It is a line chart. So column A has the dates, and column B has the values. Both have length 100. Now, column C can be considered to be the "flag" column. If a cell in Column C has the flag set to true , then the corresponding date/data values in the chart needs to be highlighted or formatted differently
I tried playing around with conditional formatting of x-axis but could not make any progress. Do I need to create a new data series with the C column?
Also given that there are 100 date values, I dont want all x-axis points to be listed on the chart. I guess excel by default shows the date points as the user increases/decreases size. But I dont want the specifically formatted date points to be skipped .
View 5 Replies
View Related
Sep 11, 2007
Following my bosses recent charting attempts involving multicoloured backgrounds, graduated bars, textured boxes, mis-matched fonts etc, etc, which frankly showed no information whatever, I was asked to simplify them.
I did so, as in the two attachments, but the response is now along the lines of "well, yes, but they aren't very exciting, are they?"
View 4 Replies
View Related
Apr 14, 2008
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies
View Related
Oct 12, 2013
I have an Excel chart graphing two data records. As the data points cross, one is in "back" and one is in "front". I would like to reverse the current layout. Is this possible?
View 1 Replies
View Related
May 16, 2008
Background: I have a Chart sheet with a combination bar/line chart on it. Data is coming from a "datasheet". There is a combo box on the chart sheet that allows you to choose a specific office whose data you want to view. Pick an office from the dropdown and the chart changes. The data is a count of closed cases by month. The data sheet shows 22 Offices with their monthly data, along with entries for each month of the Upper Control Point, Lower Control Point and Mean based on the nationwide data for that month.
The chart itself uses bars to show the office's case count per month, and I've incorporated 3 additional data series to plot the UCP, LCP and Mean as lines for each month.
I'd like to change the color of the bars to be Blue, Green or Red depending on where they fall in the 3 lines. I sort of have it working, but I'm stuck.
The code below grabs the value of each series point and compares it to the 3 control points and does change the colors. I've figured out how to use the If, next to change from one series point to the next, but cannot figure out how to loop through each of the control points. As it stands now, it looks only at the first month's control point and uses that for each successive month.
I hope this is clear and I've tried to keep it as short as possible while still providing enough information.
Sub chartbarformat()
'
' chartbarformat Macro
' Macro recorded 5/15/2008 by vacoyoungd
'
Dim x As Integer
Dim val As Variant
Dim AveragePending As Range
Dim LowerPending As Range
Dim UpperPending As Range
View 8 Replies
View Related
Jun 12, 2009
Does anyone know if there's a way to color code points on a scatter chart based on their values in a "3rd dimension" (i.e. other than the 2 dimensions represented by the chart axes).
You might imagine a scatter chart that plots transactions by price (y) and units purchased (x).
In that scenario, it might also be nice to color code each point by the region that made the sale.
The only way I know to do this is to manually create separate columns of data for each region, so that each region can be plotted on the scatter chart as a separate data series.
But since I'd like to experiment with several color coding alternatives, I'm wondering if there isn't a simpler way...
For instance, Rob Bovey's "XY Chart Labeler" can add data point labels to a scatter chart...
Is there, perhaps, another add-in that might enable color coding of data points based on a 3rd set of values?
View 9 Replies
View Related
Feb 21, 2014
how to get a line in a graph to become a curved line between 2 data points in 10 point chart. I the pictuer attached I am trying to get the line to curve at the second data point 5(ms) and the third data point at 80(ms). The aftr 80 the line become staright again.[IMG][/IMG]
View 3 Replies
View Related
Jun 22, 2007
if it actually possible to colour data points depending on the colour of the cell of the data it refers to? if not is it possible to colour it any other way
i have uneven amounts of data for weach month but still want the months to be displayed, this is the best way i can think of of doing this
View 4 Replies
View Related
Jul 27, 2012
I use data in column A:B to draw stacked line chart . My question: I want to add column C Evaluation on every point on stacked line chart.
Sheet1
*
A
B
C
1
*
Sales
evaluation
[Code] ...
View 4 Replies
View Related
Sep 11, 2012
I'm trying to write a macro that will format my chart data based on the corresponding cell value. I have the following code, which runs without error, but the formatting on data points doesn't change. I can't seem to get this to actually change the color of the data point in the chart.
Code:
Sub FormatChart()
Dim wb As Workbook
Dim ws As Worksheet
Dim wks As Worksheet
Dim y As Long
Dim j As Long
'this routine will format the charts on worksheets "SL11730" & "SL14100" 'so that data points equal to or above 98% will be green filled and 'all other points will be red filled
'set loop counter to two (contract (4100) & retail (1730))
j = 1
'start loop
Do Until j = 3
If j = 1 Then strwkb = "SL11730"
If j = 2 Then strwkb = "SL14100"
[code].....
View 4 Replies
View Related
Aug 23, 2013
How to add high/low points to a line graph, similar to what sparklines have. The file I am using now doesn't allow me to add new columns of data, so I'm trying to figure something out that just updates dynamically using the data I already have in place.
This page has the thing I want to do, but it requires additional columns to be added to work, which I don't want:
Excel - Show High/Low Points on a Line Chart
View 2 Replies
View Related
May 16, 2008
I have a VBA script that walks through the series of points on a chart and sets color based on other parameters unique to each point. Unfortunately, this doesn't see to work.
Running the following code, the .MarkerBackgroundColor and .MarkerForegroundColor commands seem to have no effect:
With myChtObj.Chart.SeriesCollection(1).Points(Counter)
.MarkerSize = 8
.MarkerStyle = xlDiamond
.Shadow = True
.MarkerBackgroundColor = RGB(250, 250, 250) ' These have no effect
.MarkerForegroundColor = RGB(0, 0, 0) ' These have no effect
.MarkerTransparency = 0
End With
Setting these parameters for the series as a whole does work, but not for individual points. Is this a known issue? Is this unique to the Mac version of Excel?
View 4 Replies
View Related
May 10, 2008
I would like to find the intersection points of all of the linesshown on the included chart. If that could be output into a table, by the day, that would be ultimately what I am after. http://bladeseri.50megs.com/Intersects.png. Guess 50megs.com doesn't allow this type of reference. Here is Geocities link http://www.geocities.com/bladex2007/Intersects.png
View 5 Replies
View Related
Oct 13, 2007
would like the points in my scatter chart to be displayed
with the same font color that is in the Data cells being charted.
the cells for the scatter chart contain 2 font colors - red and blue
e.g.
Cell Cell
a2 is red b2 is red
a3 is blue b3 is blue
View 4 Replies
View Related
Jul 26, 2012
I am trying to format the colors of the bubbles on my bubble chart to Green (>5), Yellow (4-5), or Red (<4) based on the value used to create the bubblesize.
The problem that I am having is that .BubbleSizes is property of type String, not range like .xValues, or .Values. So instead of pulling in the value, I am pulling in the reference and getting a "Type Mismatch" Error. The reference of my data for .BubbleSizes is $D$5:$D51. Each row is it's own series and the list is dynamic, so I need to be able to support future rows without a lot of maintenance.
Here is the code that I have so far.
VB:
Sub DataSeriesFormat()
'
' DataSeriesFormat Macro
' Format Data Series based on defined parameters for Green, Yellow, and Red. Created by Derek Steinmetz 7/24/12
'
Dim x As Integer
Dim val As Variant
Dim Green As Range
[Code] ......
View 1 Replies
View Related
Jul 13, 2007
Conditional formatting is lacking. I need to colorize each data point on the graph according to a characteristic cell (not an x-axis or y-axis variable). I have done something similar by simply graphing a group of points as a series and changing the series color. I'm looking for a more graceful solution. From the example picture attached (i would attach a workbook, but the data is sensitive), a point is charted based on two variables and colorization is attempted based on the third variable. i would find the range of the third value, assign a gradient based on the spread, and colorize each point. My VBA syntax is lacking for chart functions
View 7 Replies
View Related
Oct 1, 2009
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 Related
Jan 20, 2014
I 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 Related
Jan 4, 2010
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 Replies
View Related
Jul 28, 2006
I 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
View 4 Replies
View Related
Feb 17, 2014
How do I write a bar chart which reads the format of a cell instead of the content? ie I want to count how many cells in each column are amber. Amber cells have already been decided by conditional formatting.
See demo attached : bar chart demo.xlsx
View 6 Replies
View Related
Apr 22, 2014
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.
View 4 Replies
View Related
Sep 24, 2009
I 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.
View 2 Replies
View Related
May 12, 2013
I want to draw a chart in Excel, in X axis Dates and in Y axis some other data. The dates which fall Saturday and Sunday will show in RED colour. Other dates will show in default colour.
View 7 Replies
View Related
