How to add date labels and have different color points for scatterplot

Brad23

New Member
Joined
Sep 19, 2014
Messages
1
Hi all,

I'm not really familiar with VBA and am struggling at the moment. What I'm aiming to be able to do is have a scatter plot that displays date labels for each point as well as distinguish the points by year by using different colors. I found a VBA code for scatter plot date labels and I also realize that (for me at least), the easiest way to distinguish the years by color would be to separate them with a logical statement and let the chart do the rest of the work by categorizing them as separate data ranges. However, I tried to do this with the VBA code that I found and it expectedly did not work. I'm not sure if it's an easy fix to just add more "xvalues" columns in the code or if what I want to do here is even possible.

Any help would be much appreciated!

Format and Code for the Date Labels:

Date TimeRainfall (mm)24 hr RDII Volume (L/ha/day)
11/06/0673.37,482.0
01/02/0771.68,645.7
01/19/0733.36,071.8
01/23/0746.36083.7
02/15/0742.43521.9
02/19/0729.12856.2
03/11/0786.113,397.6
10/31/1243.56,813.8
11/03/1232.86,337.0
11/20/1226.54,174.3
12/01/1232.55391
12/20/1246.39774.4

<tbody>
</tbody>


Sub AttachLabelsToPoints()


'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String


' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False


'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula


'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop


'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter


End Sub

Format to try to get different colored points based on year (just took a smaller section):

Date Time06/07 Rainfall12/13 Rainfall13/14 Rainfall24 hr RDII Volume (L/ha/day)Rainfall (mm)
11/06/0673.3#N/A#N/A7,482.073.3
01/02/0771.6#N/A#N/A8,645.771.6
01/19/0733.3#N/A#N/A6,071.833.3
01/23/0746.3#N/A#N/A6083.746.3
02/15/0742.4#N/A#N/A3521.942.4
02/19/0729.1#N/A#N/A2856.229.1
03/11/0786.1#N/A#N/A13,397.686.1
10/31/12#N/A43.5#N/A6,813.843.5
11/03/12#N/A32.8#N/A6,337.032.8
11/20/12#N/A26.5#N/A4,174.326.5
12/01/12#N/A32.5#N/A539132.5
12/20/12#N/A46.3#N/A9774.446.3

<tbody>
</tbody>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top