different color for scatterplot with 1 series

[Laoballer]

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,

 

[Bernard Liengme]

Jon tells all at
http://peltiertech.com/Excel/Charts/ConditionalChart1.html
best wishes

 

[Shane Devenshire]

Hi,

Actually the answer to your first question would be to choose the Vary color
by point options. But since your second question suggests that you want to
conditionally format the points, with only one series, the solution at Jon's
website won't work because you are producting in effect different series for
each time period.

Why exactly do they need to be one series? In scatter charts that seems to
be unnecessary.

You can format the individual points by selecting the series and then the
data point and formatting that one point. Then without breaking the
selection press left or right cursor keys and if you want the newly selected
point to be the same color press F4. Repeat until all the points are
formatted as desired. It's pretty quick if you don't have 4000 data points.

 

[PBezucha]

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

 

[PBezucha]

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

 

[Laoballer]

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,
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,