8

I have this chart in which if any point in graphs exceeds specific limit then its color should change.

enter image description here


can anyone suggest me how to get the chart in VBA and then apply this kind of condition e.g i want to change the color of highest point in the above graph . Any help would be highly appreciated.

Mushahid Hussain
  • 4,052
  • 11
  • 43
  • 62
  • record a macro of changing the point color?? – Aprillion Jun 21 '12 at 16:33
  • possible duplicate of [How do I programmatically change the line colour for a series in a chart in Excel 2007](http://stackoverflow.com/questions/289122/how-do-i-programmatically-change-the-line-colour-for-a-series-in-a-chart-in-exce) – Mohammed Azharuddin Shaikh Jun 22 '12 at 04:59

2 Answers2

15

Using: ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart1").Chart.SeriesCollection(1)

Color of each point is .Points(PointNumber).Interior.Color

The number of points you have to cycle though is .Points.Count

The value of each point is .Points(PointNumber).Value

colors of the markers themselves (Applies only to line, scatter, and radar charts):

.Points(PointNumber).MarkerBackgroundColor = RGB(0,255,0)    ' green
.Points(PointNumber).MarkerForegroundColor = RGB(255,0,0)    ' red
.Points(PointNumber).MarkerStyle = xlMarkerStyleCircle ' change the shape
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • thanks for your help. but could you please elaborate what's the purpose of .seriescollection(1) here? and how to join these sample codes to acheive my target? i am new in VBA so didn't get it properly. – Mushahid Hussain Jun 21 '12 at 16:00
  • `seriescollection(1)` is the list of the first set of your data points used in making the graph. loop though the points, check the value, and change the color if it's within your criteria – SeanC Jun 21 '12 at 16:04
  • ActiveWorkbook.Sheets("LDL-C").ChartObjects("Chart 1047").chart.SeriesCollection(1).Points(1).Interior.Color = RGB(20, 20, 20) do you see any problem in this? it's not working for me?? – Mushahid Hussain Jun 21 '12 at 16:17
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12868/discussion-between-sean-cheshire-and-james) – SeanC Jun 21 '12 at 16:31
5

Let's take another approach, which does not require any code.

Assume your data is in columns A (sequence number or time) and B value, starting in A2 and B2, since your labels are in A1 and B1. We'll add a series to the chart that includes any deviant values from column B. This series will draw a marker in front of any deviant points so the original point will still be present, and instead of reformatting this point the new series displays a point.

In cell C1, enter "Deviant".

In Cell C2, enter a formula that detects a deviant point, something like:

=IF(AND(B2>upperlimit,B2

This puts the value into column C if column B exceeds upper and lower limits, otherwise it puts #N/A into column C, #N/A will not result in a plotted point.

Copy the data in column C, select the chart, and Paste Special as a new series. Format this series to have no line and whatever glaring marker you want to use to indicate an out of control point.

Jon Peltier
  • 5,895
  • 1
  • 27
  • 27
  • Excellent proposition, which helped me to produce - like in sparklines - the markers for "min", "max", "first" and "last" data point in a line chart. There is one line of data points which are shown in the line chart, but without markers, and there is a 2nd line of data points which refer per formula to first, last, min and max values, but show the #N/A error code for all other cells. These data are plotted without line, but with markers, so that both lines are superposed. Works very well. – domke consulting Nov 07 '14 at 18:24