colouring scatter chart series in vba

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a scatter chart using 3 columns of data (KF17 to KH 300)
The first column KF is Clusters (Will always be 1 to 10, but variable on how mans rows are in each cluster). There are the series
Column KG is the x axis
Column KH is the Y axis.
I have a macro that copies the range into KF to KH, which then changes the new positions on the chart for each of the 10 series (Clusters)
The problem I have is the colours for the series are doing strange things.
With a new set of data copied in by the macro and sorted into Clusters (Series) 1 to 10
Two of the clusters despite when you hover (e.g. Series 3 and Series 8) have both got the same colour. If I try to change the colour of one of the series 3 or 8 they both change to that colour.
It only appears to be with two of the series all the others are ok.
The chart is dynamic for the x and y axis data and when I check if the series are all correct they appear to be so.
This is an example of the named. I have a difference name for each cluster x and cluster Y.
For e.g. this is Cluster 1 for the x axis (so I have 20 of these name ranges). =OFFSET(Combined!$KG$16,MATCH(1,Combined!$KF$17:$KF$517,0),,COUNTIF(Combined!$KF$17:$KF$517,1))
Any thoughts on why it would take two of the series and treat them as if they were one?

Any help would be greatly appreciated (something tells me it is to do with the vba copying of the data and then sorting the data, but I cannot figure it out). I wondered if I should get the vba code to also colour the series?

This is my vba code for copying the data -

Private Sub CommandButton1_Click()
'declare a variable for the last row
Dim IngLastRow As Long


Sheets("Combined").Select
Range("KF1:KH1").Select
Application.CutCopyMode = False
Selection.Copy
'Sheets("PriceLists").Select
Range("KF17:KH17").Select
ActiveSheet.Paste
Application.CutCopyMode = False

lngLastRow = Range("JZ" & Rows.Count).End(xlUp).Row
Range("KF17:KH" & lngLastRow).FillDown

Range("KF17").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues



'Sub SortDataWithoutHeader()
Range("KF17:KH17", Range("KF17:KH17").End(xlDown)).Sort Key1:=Range("KF17"), Order1:=xlAscending, Header:=xlNo
Range("KF1").Select


End Sub
 

Attachments

  • XYScatter.PNG
    XYScatter.PNG
    43.9 KB · Views: 3

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top