Conditionally format points based on a third column
 

I would like the color of my data points to vary based not on their x or y
value, but rather on a third column running alongside them. How can I do it?

Conditionally format points based on a third column
 

Assuming your third column is column "C" and that you would want to change
the colors of the XY points based on the names Jim, Frank, or Kim, a VBA
option could look like this (does not include any error handling):

Sub ChangeXYColors()
Application.ScreenUpdating = False
Dim Rng As Range
Cnt = 1
For Each Rng In Range("C2:C10")
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.SeriesCollection(1).Points(Cnt).Select
If Rng.Value = "Jim" Then
With Selection
.MarkerBackgroundColorIndex = 10
.MarkerForegroundColorIndex = 10
End With
ElseIf Rng.Value = "Frank" Then
With Selection
.MarkerBackgroundColorIndex = 11
.MarkerForegroundColorIndex = 11
End With
ElseIf Rng.Value = "Kim" Then
With Selection
.MarkerBackgroundColorIndex = 12
.MarkerForegroundColorIndex = 12
End With
End If
Cnt = Cnt + 1
Next Rng
ActiveChart.Deselect
End Sub

--
John Mansfield
http://cellmatrix.net





"hmm" wrote:

I would like the color of my data points to vary based not on their x or y
value, but rather on a third column running alongside them. How can I do it?

Conditionally format points based on a third column
 

In addition to the VBA example, I've added a worksheet example to my site -
the entry is dated today.

--
John Mansfield
http://cellmatrix.net





"John Mansfield" wrote:

Assuming your third column is column "C" and that you would want to change
the colors of the XY points based on the names Jim, Frank, or Kim, a VBA
option could look like this (does not include any error handling):

Sub ChangeXYColors()
Application.ScreenUpdating = False
Dim Rng As Range
Cnt = 1
For Each Rng In Range("C2:C10")
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.SeriesCollection(1).Points(Cnt).Select
If Rng.Value = "Jim" Then
With Selection
.MarkerBackgroundColorIndex = 10
.MarkerForegroundColorIndex = 10
End With
ElseIf Rng.Value = "Frank" Then
With Selection
.MarkerBackgroundColorIndex = 11
.MarkerForegroundColorIndex = 11
End With
ElseIf Rng.Value = "Kim" Then
With Selection
.MarkerBackgroundColorIndex = 12
.MarkerForegroundColorIndex = 12
End With
End If
Cnt = Cnt + 1
Next Rng
ActiveChart.Deselect
End Sub

--
John Mansfield
http://cellmatrix.net





"hmm" wrote:

I would like the color of my data points to vary based not on their x or y
value, but rather on a third column running alongside them. How can I do it?

Conditionally format points based on a third column
 

Thanks John.

"John Mansfield" wrote:

In addition to the VBA example, I've added a worksheet example to my site -
the entry is dated today.

--
John Mansfield
http://cellmatrix.net





"John Mansfield" wrote:

Assuming your third column is column "C" and that you would want to change
the colors of the XY points based on the names Jim, Frank, or Kim, a VBA
option could look like this (does not include any error handling):

Sub ChangeXYColors()
Application.ScreenUpdating = False
Dim Rng As Range
Cnt = 1
For Each Rng In Range("C2:C10")
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.SeriesCollection(1).Points(Cnt).Select
If Rng.Value = "Jim" Then
With Selection
.MarkerBackgroundColorIndex = 10
.MarkerForegroundColorIndex = 10
End With
ElseIf Rng.Value = "Frank" Then
With Selection
.MarkerBackgroundColorIndex = 11
.MarkerForegroundColorIndex = 11
End With
ElseIf Rng.Value = "Kim" Then
With Selection
.MarkerBackgroundColorIndex = 12
.MarkerForegroundColorIndex = 12
End With
End If
Cnt = Cnt + 1
Next Rng
ActiveChart.Deselect
End Sub

--
John Mansfield
http://cellmatrix.net





"hmm" wrote:

I would like the color of my data points to vary based not on their x or y
value, but rather on a third column running alongside them. How can I do it?