On Jan 29, 11:56*pm, PBezucha
wrote:
Just for case you would have problems with time variables:

Sub MarkerConditional()

' Sub changes the colors of individual markers in the selected series
' of x-y chart, according to the linked conditions. The controlling values
' must be ranged along with y-values in the distance OffsetCol

Dim SerPoints As Points, Ys As Range
Dim ErrMsg As String, SerPointsFormula As String, Rng As String
Dim I As Long, NPoints As Long, ColIndex As Long
Dim LLimit As Double, ULimit As Double
' select color indexes
Const Red As Long = 3, Blue As Long = 5
' set the offset between the y- and controlling columns
' (1 for adjacent)
Const OffsetCol As Long = 1

' get the limits for classification from cells
LLimit = Range("D1").Value
ULimit = Range("D2").Value

On Error GoTo ErrExit
ErrMsg = "No series has been selected"
Set SerPoints = Selection.Points
NPoints = SerPoints.Count
' analysis of Series formula
SerPointsFormula = SerPoints.Parent.Formula
I = 3
Do
* I = I + 1
* Rng = Right(SerPointsFormula, I)
Loop Until Left(Rng, 1) = "!"
Rng = Right(Rng, Len(Rng) - 1)
Rng = _
* Left(Rng, Application.WorksheetFunction.Search(",", Rng) - 1)
Set Ys = Range(Rng)
For I = 1 To NPoints
' use SelectCase as the best method of classification -
' plenty of conditions - colors - may be applied
' values of the controlling range are tested and linked to colors
* Select Case CDbl(Ys.Cells(I).Offset(0, OffsetCol).Value)
* Case LLimit To ULimit: ColIndex = Red
* Case Else: ColIndex = Blue
* End Select
' in case both background and foreground colors should be the same
* SerPoints(I).MarkerBackgroundColorIndex = ColIndex
* SerPoints(I).MarkerForegroundColorIndex = ColIndex
Next I
Exit Sub
ErrExit:
MsgBox ErrMsg
On Error GoTo 0
End Sub
--
Petr Bezucha

"PBezucha" wrote:
Leoballer,

Your anticipation of the benefit of VBA is correct. After creating the
normal xy chart and selecting the appropriate series, run the following
macro. It needs, of course, previous customization, which may be still lucid.

Sub MarkerConditional()

' Sub changes the color of each marker from the selected series
' of x-y chart, according to the linked condition. The controlling values
' must be ranged along with y-values in the column distance OffsetCol

Dim SerPoints As Points, Ys As Range
Dim ErrMsg As String, SerPointsFormula As String, Rng As String
Dim I As Long, NPoints As Long, ColIndex As Long

' define color indexes
Const Red As Long = 3, Blue As Long = 5

' define the offset between the y- and controlling columns
' (1 for adjacent)
Const OffsetCol As Long = 1

' define the boundary for classification
Const Limit As Double = 10

On Error GoTo ErrExit
ErrMsg = "No series has been selected"
Set SerPoints = Selection.Points
NPoints = SerPoints.Count

' analysis of Series formula
SerPointsFormula = SerPoints.Parent.Formula
I = 3
Do
* I = I + 1
* Rng = Right(SerPointsFormula, I)
Loop Until Left(Rng, 1) = "!"
Rng = Right(Rng, Len(Rng) - 1)
Rng = *Left(Rng, Application.WorksheetFunction.Search(",", Rng) - 1)
Set Ys = Range(Rng)
For I = 1 To NPoints

' here Select Case is probably the best method of classification -
' variety of conditions - colors - can be applied

' values of the controlling range are tested and linked to colors:

* Select Case Ys.Cells(I).Offset(0, OffsetCol).Value
* Case Is < Limit: ColIndex = Red
* Case Else: ColIndex = Blue
* End Select

' in case both background and foreground colors should be the same:
* SerPoints(I).MarkerBackgroundColorIndex = ColIndex
* SerPoints(I).MarkerForegroundColorIndex = ColIndex

Next I
Exit Sub

ErrExit:
MsgBox ErrMsg
On Error GoTo 0
End Sub

Regards
Petr Bezucha

"Laoballer" wrote:

is there a way to plot different colors for individual points on a
scatterplot with only 1 series?

my data looks like this

x * *y * *time

I would like to distinguish the color of the points by the time, *e..g.
anything between 8am - 5pm one color and everything else another
color. *I know you can do this with two series if I create a series
with time that's 8am-5pm and another with the other data. *I'm
thinking I would need to program using vba, but not sure where to
start.

Thanks,

Petr,
Thanks for the help, I'll give this a shot, right now I have to do 3
total queries, one to get the data to generate a trendline, and two
more for the two separate time category. If I can use your code it
would really speed things up.

Thanks,