Colour coding points on a scatter graph depending on a value in a cell

Can be done by macro. Suggest uploading a copy of the workbook so something can be coded.

There is a worksheet of student details including whether they are male of female, M and F. Is it possible to get the points on the scatter graph to appear red for girls and blue for boys based on the value that appears in these cells without manually doing it myself?

It is not something that Excel will do automatically. I always thought that the easiest way to do this was to separate the data into two data sets, one for M and one for F, then plot those as two different data series. By default, Excel will format each data series with different colors.

If you decide that the data must be plotted as a single series, then it will require a macro as mrice suggests.

Try this in a new module

Please Login or Register  to view this content.

Hello,

I am having trouble using this code, I have had a play around with it and it seems to work fine on its own but I am trying to use

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Application.ScreenUpdating = False

Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long


Me.GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = 3 Then
ActiveChart.SeriesCollection(Arg1).Points(Arg2).ApplyDataLabels
ActiveChart.SeriesCollection(Arg1).Points(Arg2).DataLabel.Font.Size = 12
Z = 0
For N = 5 To Sheets("Actual Grade").Cells(Sheets("Actual Grade").Rows.Count, 1).End(xlUp).Row
If Sheets("Actual Grade").Cells(N, 1).Height > 0 Then
Z = Z + 1
If Z = Arg2 Then Exit For
End If
Next N
ActiveChart.SeriesCollection(Arg1).Points(Arg2).DataLabel.Text = Sheets("Actual Grade").Cells(N, 1)
Else
On Error Resume Next
ActiveChart.SeriesCollection(1).DataLabels.Delete
On Error GoTo 0
End If
Application.ScreenUpdating = True

End Sub

As well as this code which is when it doesn't work, can the two codes work together to achieve the colour coding affect?

Regards Stephen

Not sure I understand what you are trying to do. The colour coding is a one off activity which you do the the chart when its created but the label appearance and disappearance is dynamic.

This is getting a bit complex. Can you upload a real copy of the workbook (with substitute names) as it is difficult without something to test on.

The data range that your graphs are using is one row shorter than the data that the colour coding is working off. The code seems to work correctly if this is changed.

(As a former school governing body Chair, the data looks very familiar )

The data range from my graphs will constantly change as I filter the data, can the code accommodate this and if so how?

What sort of function did you have in your spreadsheet? Did you have the same sort of graphical output? If you don't mind me asking Hopefully this spreadsheet will prove useful when finished

Suggest using this for colour coding

Please Login or Register  to view this content.

and these ranges for the graph

='Predicted Grade'!$N$5:$N$20

='Actual Grade'!$R$5:$R$20

I agree with MrShorty-plotting two series is easier even though it takes a little time to set up

change the Else part of the mouse move code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The females are still permanently visible. Would the whole code need to be changed as it is plotting two different series, meaning there are two different labels?

no but you need to make sure you update the code in all your chart sheets

Separating males and females into different series works perfectly but seems a bit tedious when changing the data being looked at in the graph. Can the original code accommodate the filtering?