VB for scatter graph help
 

Hi all,

I've been trying to write some vb to autogenerate a scatter graph where the
data is list in one worksheet. The data comprises dates and values bewtween
1 and 25. The x-axis is the timeline and the y axis is 0-25. There have to
be 4 series where for example 20-25 are coloured red, 15-19 are yellow and
so on. Can anyone point my in the right direction....

Help much appreciated....

Chris

VB for scatter graph help
 

Here is an example that sets the first two colours.

Note that I have created a helper column to split out the values so that
separate series are created.

Sub CreateScatterChart()
Dim myChart As Chart

Range("C1:G1").Value = Array("20", "15", "10", "5", "0")
Range("C2").FormulaR1C1 = "=IF(AND(RC2=20,RC2<=25),RC2,NA())"
Range("D2").FormulaR1C1 = "=IF(AND(RC2=15,RC2<20),RC2,NA())"
Range("E2").FormulaR1C1 = "=IF(AND(RC2=10,RC2<15),RC2,NA())"
Range("F2").FormulaR1C1 = "=IF(AND(RC2=5,RC2<10),RC2,NA())"
Range("G2").FormulaR1C1 = "=IF(AND(RC2=0,RC2<5),RC2,NA())"
Range("C2:G2").AutoFill Destination:=Range("C2:G20")
Set myChart = Charts.Add
With myChart

.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("Sheet2").Range("A1:A20,C1:G20")
.Location Whe=xlLocationAsObject, Name:="Sheet2"
End With

With ActiveChart

With .SeriesCollection(1)
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
End With

With .SeriesCollection(2)
.MarkerBackgroundColorIndex = 5
.MarkerForegroundColorIndex = 5
End With
End With
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chris" wrote in message
...
Hi all,

I've been trying to write some vb to autogenerate a scatter graph where
the data is list in one worksheet. The data comprises dates and values
bewtween 1 and 25. The x-axis is the timeline and the y axis is 0-25.
There have to be 4 series where for example 20-25 are coloured red, 15-19
are yellow and so on. Can anyone point my in the right direction....

Help much appreciated....

Chris

VB for scatter graph help
 

Did you try the macro recorder? That can help get you started.

Then check out:
http://peltiertech.com/Excel/ChartsH...kChartVBA.html

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


"Chris" wrote in message
...
Hi all,

I've been trying to write some vb to autogenerate a scatter graph where
the data is list in one worksheet. The data comprises dates and values
bewtween 1 and 25. The x-axis is the timeline and the y axis is 0-25.
There have to be 4 series where for example 20-25 are coloured red, 15-19
are yellow and so on. Can anyone point my in the right direction....

Help much appreciated....

Chris