VB for scatter graph help

[Chris]

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

 

[Bob Phillips]

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 Where:=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)

 

[Jon Peltier]

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

Then check out:
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon