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.
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?
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?
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?
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 .
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?"
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?
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?
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
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?
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]
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
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"
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:
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?
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
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
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
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
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.
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?
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.
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.
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.
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.
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.
