Conditional Charting

[angy345]

Hi, i hope someone will be able to help me on that one. I want on the
line graph to show the data points that are above and below the average
control line to be of a diffenrent colour. For the moment i am only
trying to the points that are equal to between 0 and 7 to be of a
different colour.
Below is the code to do so but i get an error msg saying that the
object doesnt support method or property. Can anyone tell me where am i
going wrong here.

Sub ComponentValue()

Dim i As Integer, CompVal As Integer
Dim dpoint As Point

i = 1

For Each dpoint In ActiveChart.SeriesCollection(4).Point(i)
CompVal = WorksheetFunction.Max(dpoint)

If CompVal >= 1 Or CompVal <= 7 Then
With ActiveChart.SeriesCollection(4).Points(i)
..MarkerBackgroundColorIndex = 3
..MarkerForegroundColorIndex = 3
i = i + 1
End With
End If

Next

End Sub

Any help and comment are most welcome
Thank you in advance
Angel

 

[Tushar Mehta]

You don't need code for this. Suppose the original data are in A1:A10.
Then, in B1 enter the formula =IF(AND(A1>=0,A1<=7),A1,NA()) In C1
enter the formula =IF(ISNA(B1),A1,NA()) Copy B1:C1 down to rows 2:10.
Plot A1:C10 as three series. Format the series corresponding to the
data in column A to use a Line with no Markers. For the series
corresponding to column B, use No Line with Green Markers. For the
last series, use No Line and Red Markers.

If you must do it programmatically, here are some pointers relative to
your code/post:

When you share the error message, it doesn't hurt to indicate which
line returns the error.

The Max function finds the maximum of just one point. A point is an
object, not a numerical value. The max function doesn't understand
objects, just numbers.

Bracketed inside a With clause, you need to specify further references
to that object with a leading period (.)

Please format your code for readability.

Finally, it is often easier to get the necessary syntax by using XL's
macro recorder.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Angel -

I have the steps worked out for you on this web page:

http://www.geocities.com/jonpeltier/Excel/Charts/ConditionalChart1.html

- Jon