CODEWITHSUNDEEP
excelvbagraph
Mary
Mary

Reputation: 788

Different colour between series VBA Scatter Graph

I have the following macro which plots a Scatter graph for three columns. One column (AL13, downwards) is on the x axis. How do I get it to plot the other two columns (AK and AM) onto the same scatter? Also in different colour to each other? Thank You

Sub Graphing()

        Set rng4 = ActiveSheet.Range(Range("AP13"), Range("AV33"))

With ActiveSheet.ChartObjects.Add(Left:=rng4.Left, Width:=rng4.Width, Top:=rng4.Top, Height:=rng4.Height)
    .Chart.ChartType = xlXYScatter
    .Chart.HasLegend = False
    .Chart.Axes(xlCategory).TickLabels.Font.Size = 18
    .Chart.Axes(xlValue).TickLabels.Font.Size = 18
    Set srs = .Chart.SeriesCollection.NewSeries
    srs.Values = Range(Range("AK13"), Range("AK13").End(xlDown))
    srs.XValues = Range(Range("AL13"), Range("AL13").End(xlDown))
    srs.Values = Range(Range("AM13"), Range("AM13").End(xlDown)) 

End With
End Sub

Upvotes: 0

Views: 841

Answers (2)

David Zemens
David Zemens

Reputation: 53623

I will repost the code that I revised for you above, thanks for crediting me :)

Sub Graphing()
'Declare all the variables to be used:'
Dim rng4 as Range
Dim srs as Series
Dim cht as Chart
Dim xVals as Range
Dim srsVals as Range 

'Set the chart's data range:'
Set rng4 = ActiveSheet.Range(Range("AP13"), Range("AV33"))

'Set the range variable to contain the series values'
' You can later modify this to include any number of columns, and the '
'  loop structure below will add each column as a series to the chart.'
Set srsVals = ActiveSheet.Range(Range("AL13"),Range("AM13").End(xlDown))

'Set the cht variable:'
Set cht= ActiveSheet.ChartObjects.Add(Left:=rng4.Left, Width:=rng4.Width, Top:=rng4.Top, Height:=rng4.Height).Chart

'Set the Range variable for xValues:
Set xVals = Range(Range("AK13"),Range("AK13").End(xlDown))

'Format the chart and add series to the chart by iterating over the columns in srsVals:'
With cht
    .ChartType = xlXYScatter
    .HasLegend = False
    .Axes(xlCategory).TickLabels.Font.Size = 18
    .Axes(xlValue).TickLabels.Font.Size = 18

    'Create the series in a loop
    For c = 1 to srsVal.Columns.Count
        Set srs = .SeriesCollection.NewSeries
        With srs
            .Values = xVals
            .XValues = Range(srsVals.Columns(c).Address)
            .Name = "Series " & c '<-- Modify as needed.'
        End With
    Next

End With
End Sub

Upvotes: 1

Mary
Mary

Reputation: 788

I found that if I set the series as two separate series then it will plot both and give them different colours. Not sure if it is the most efficient way of doing it but it works.

    Sub Graphing()
    'Declare all the variables to be used:'
    Dim rng4 as Range
    Dim srs as Series
    Dim cht as Chart
    Dim xVals as Range
    Dim srsVals as Range 

    'Set the chart's data range:'
    Set rng4 = ActiveSheet.Range(Range("AP13"), Range("AV33"))

    'Set the range variable to contain the series values'
    ' You can later modify this to include any number of columns, and the '
    '  loop structure below will add each column as a series to the chart.'
    Set srsVals = ActiveSheet.Range(Range("AL13"),Range("AM13").End(xlDown))

    'Set the cht variable:'
    Set cht= ActiveSheet.ChartObjects.Add(Left:=rng4.Left, Width:=rng4.Width, Top:=rng4.Top, Height:=rng4.Height).Chart

    'Set the Range variable for xValues:
    Set xVals = Range(Range("AK13"),Range("AK13").End(xlDown))

    'Format the chart and add series to the chart by iterating over the columns in srsVals:'
    With cht
        .ChartType = xlXYScatter
        .HasLegend = False
        .Axes(xlCategory).TickLabels.Font.Size = 18
        .Axes(xlValue).TickLabels.Font.Size = 18

        'Create the series in a loop
        For c = 1 to srsVal.Columns.Count
            Set srs = .SeriesCollection.NewSeries
            With srs
                .Values = xVals
                .XValues = Range(srsVals.Columns(c).Address)
                .Name = "Series " & c '<-- Modify as needed.'
            End With
        Next

    End With
End Sub

Upvotes: 1

Related Questions