Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
different color for scatterplot with 1 series
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, |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
different color for scatterplot with 1 series
Jon tells all at
http://peltiertech.com/Excel/Charts/...nalChart1.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Laoballer" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
different color for scatterplot with 1 series
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "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, |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
different color for scatterplot with 1 series
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, |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
different color for scatterplot with 1 series
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, |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
different color for scatterplot with 1 series
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating scatterplot points by color and shape | Charts and Charting in Excel | |||
different color for 1 series | Charts and Charting in Excel | |||
define series in scatterplot from column | Charts and Charting in Excel | |||
help me to change the color of two series in a chart into the same | Charts and Charting in Excel | |||
Changing Series Color | Charts and Charting in Excel |