Change marker color in excel

[vtmike]

Hi,

I am just starting to use macros in microsoft excel 2007. Does anyone have tips on how I can change the marker color in a XY scatter plot?

4 20 P
5 21 F
6 22 P
7 23 P
8 24 F
9 26 F

For example if the cell in column 3 above has "P", use a green marker & if the cell in column 3 has "F", use a red marker in the plot?

 

[MintJulep]

As far as I know all the markers for any given series of data must be the same.

If you want different markers for different points you need to chart them as different series.

 

[IRstuff]

Actually, it can be done. There is a "Format Data Point" function, and the macro recorder captured this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/9/2010 by Eden Mei
'

'
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(8).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = 41
        .MarkerStyle = xlSquare
        .MarkerSize = 3
        .Shadow = False
    End With
End Sub

so, the above selected the 8th point and only changed that one point's color to a shade of blue

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[vtmike]

Well but how do I incorporate an If statement for marker color? Take following code for example for returning the text "PASS", "FAIL" or "NA" based on entries in column 3:

Sub Example4()
For x = 2 To 7
If Cells(x, 3).Value = "P" Then
Cells(x, 4) = "PASS"
Else
If Cells(x, 3).Value = "F" Then
Cells(x, 4) = "FAIL"
Else
If Cells(x, 3).Value = "" Then
Cells(x, 4) = "NA"
End If
End If
End If
Next x
End Sub

How do I apply this concept to a XY plot to change color of marker based on the text input in column 3?

 

[IRstuff]

Well, wouldn't you try to call the macro with the point number and a color index?

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[zelgar]

Couldn't you just use Conditional Formatting in the cells to highlight them?