dynamic excel chart marker line colors

[gdesreu]

Does anyone have any VBA that will alter my charts marker line color if the axis label matches a certain value?

For example; My y axis will have consistently named axis labels, such as analyte 1, analyte 2, analyte 3, 4, 5 etc., but sometimes I will not need to chart analytes 3 and 4. This means analyte 5 assumes the third position in the chart which then takes on the color of the previously charted analyte 3.
I would like to run some vba to change the chart marker line colors to match the analyte name and not be associated with the chart position.
In other words analyte 5 should always be RED no matter where it falls on the chart.

Any ideas? Thanks

 

[Derek Brown]

Perhaps you can make use of the following:
VBA Conditional Formatting of Charts by Category Label - Peltier Tech Blog

 

[gdesreu]

Perhaps you can make use of the following:
VBA Conditional Formatting of Charts by Category Label - Peltier Tech Blog

Thanks Derek this works great!!!

Sub Macro1()
  Dim rPatterns As Range
  Dim iCategory As Long
  Dim vCategories As Variant
  Dim rCategory As Range

  Set rPatterns = ActiveSheet.Range("e1:e4")
  
  With ActiveChart.SeriesCollection(1)
  vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
      .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
    Next
  End With
End Sub

Now for one last tweak, if I try specifying a chart by chart name I get an error. Any idea how to modify the active.chart.seriescollection1 line to specify charts by name?

 

[Derek Brown]

Try something like the following (replace "MyChart" as appropriate).

ActiveSheet.ChartObjects("MyChart").Select
With ActiveChart.SeriesCollection(1)
' etc.

 

[gdesreu]

Try something like the following (replace "MyChart" as appropriate).

ActiveSheet.ChartObjects("MyChart").Select
With ActiveChart.SeriesCollection(1)
' etc.

Thanks Derek, I should have know that as I have done it before. Here is the working code for reference. Exactly what I needed. Thanks for the help

Sub ColorByCategoryLabel()

  Dim rPatterns As Range
  Dim iCategory As Long
  Dim vCategories As Variant
  Dim rCategory As Range

'----this is where you reference the cells containing the color format.
Set rPatterns = Sheets("mix").Range("A5:A60")
  
'----this selects the second series of the active chart range.  Select (1) for first series etc.
  ActiveSheet.ChartObjects("chart 39").Select
  With ActiveChart.SeriesCollection(2)
       'ActiveChart.SeriesCollection(2).Select
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
'----this is where you set the marker line color around the point.  Use markerbackground to change the fill color
      .Points(iCategory).MarkerForegroundColor = rCategory.Interior.Color
    Next
  
  End With
End Sub