colouring scatter chart series in vba

[Peter Davison]

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