Macro help
 

Hi,

I am trying to figure out how to write a macro for a graph to change a point if the number falls below 80. Eg, if point a, b, c, d are at 79, 90, 78 & 60, the graphs will change the color of point a, c & d to a different color.

This is a line graph with many points.

Thanks in advance.
Aloys

I am a strong believer in writing brute force macros to make them work and
then once they do, clean them up with option explicit and dim statements, etc.
Here is a quick Brute force macro where you need to enter the details of the
data set for the chart series of interest in the macro
Worksheet name
Start row
end row
column
It also assumes series 1 is the series of interest
for my quick macro I used "<2" as my criteria.
I left in a couple of steps I ended up not using because there is
information which could be used when the macro is made more formal.

Sub m()
shtn = "Sheet1"
Srow = 1
Erow = 3
col = 1
SRN = ActiveChart.SeriesCollection(1).Formula =
"=SERIES(,,Sheet1!R1C1:R3C1,1)"
np = ActiveChart.SeriesCollection(1).Points().Count
For p = 1 To Erow - Srow + 1
If Sheets(shtn).Cells(Srow + p - 1, col) < 2 Then
ActiveChart.SeriesCollection(1).Points(p).Select
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlDiamond
.MarkerSize = 10
.Shadow = False
End With
End If
Next p
End Sub

It needs a lot of input manipulation to make it be user friendly




"Aloysicus" wrote:


Hi,

I am trying to figure out how to write a macro for a graph to change a
point if the number falls below 80. Eg, if point a, b, c, d are at 79,
90, 78 & 60, the graphs will change the color of point a, c & d to a
different color.

This is a line graph with many points.

Thanks in advance.
Aloys


--
Aloysicus

Read about conditional charts, no VBA necessary:

http://peltiertech.com/Excel/Charts/...nalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Aloysicus wrote:

Hi,

I am trying to figure out how to write a macro for a graph to change a
point if the number falls below 80. Eg, if point a, b, c, d are at 79,
90, 78 & 60, the graphs will change the color of point a, c & d to a
different color.

This is a line graph with many points.

Thanks in advance.
Aloys

I use the method described in your paper quite often. Before I found out
about the Na() option I always just put my false response outside the plot
range. and selected max and min values for the graph. From force of habit,
I still do it this way most of the time.

"Jon Peltier" wrote:

Read about conditional charts, no VBA necessary:

http://peltiertech.com/Excel/Charts/...nalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Aloysicus wrote:

Hi,

I am trying to figure out how to write a macro for a graph to change a
point if the number falls below 80. Eg, if point a, b, c, d are at 79,
90, 78 & 60, the graphs will change the color of point a, c & d to a
different color.

This is a line graph with many points.

Thanks in advance.
Aloys