Conditional Charting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

angy345 - 15 Dec 2003 10:21 GMT

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

---
Message posted from http://www.ExcelForum.com/

Reply to this Message

Tushar Mehta - 15 Dec 2003 12:48 GMT

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.

Signature

Regards,

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

> 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
[quoted text clipped - 33 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Jon Peltier - 15 Dec 2003 16:14 GMT

Angel -

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Reply to this Message