looking for some help with VBA – Select Case and our scatter chart. The code currently looks at cell ranges and creates shapes and fills those shapes with specific colors, based on CASES. However, every time a new case option for COLOR is added the code must be edited. We have over 20 options right now (i edited down the code to paste here) and i was hoping there was a way to reference a cell range instead on our “Formatting” worksheet, that the user can add to as needed.

Sub CreateColorScatterPointsCT()
On Error GoTo Er
    Dim Cht As Chart
    Dim srs As Series
    Dim pt As Point
    Dim p As Long
    Dim Vals$, lTrim#, rTrim#
    Dim valRange As Range, cl As Range
    Dim myColor As Long
    Dim myShape As String
    
    Set Cht = ActiveSheet.ChartObjects(1).Chart
    Set srs = Cht.SeriesCollection(1)

     lTrim = InStrRev(srs.Formula, ",", InStrRev(srs.Formula, ",") - 1, vbBinaryCompare) + 1
    rTrim = InStrRev(srs.Formula, ",")
    Vals = Mid(srs.Formula, lTrim, rTrim - lTrim)
    Set valRange = Range(Vals)

    For p = 1 To srs.Points.Count
        Set pt = srs.Points(p) 'Y value points
        Set shp = valRange(p).Offset(0, 1) 'Shape is in column next to Y values.
        Set cl = valRange(p).Offset(0, 1) 'Color is in the column next to Shape.
       
       With pt
               'Formats marker color, shape, background, line weight
            Select Case LCase(shp)
    
        Case "Option A"
                    myShape = xlMarkerStyleDiamond
                    .MarkerSize = 12
                    .MarkerForegroundColor = RGB(0, 0, 0)
                    .MarkerBackgroundColor = RGB(0, 0, 0)
                    .Format.Line.Weight = 0.75
                    .Border.LineStyle = xlNone
                    
        Case Else
                    myShape = xlMarkerStyleDiamond
                    .MarkerSize = 8
                    .MarkerForegroundColor = RGB(0, 0, 0)
                    .MarkerBackgroundColor = RGB(0, 0, 0)
                    .Format.Line.Weight = 0.75
                    .Border.LineStyle = xlNone
      
            End Select

            .MarkerStyle = myShape

        End With
        
        'Marker Color
        With pt.Format.Fill
            .Visible = msoTrue
            'Assign RGB color value based on the cell value (option name)
            'Add additional cases as needed
            Select Case LCase(cl)
                Case "option a"
                    myColor = RGB(0, 83, 155)
                Case "option b"
                    myColor = RGB(32, 8, 50)
                Case "Option c"
                    myColor = RGB(25, 10, 0)
                Case "option d"
                    myColor = RGB(250, 50, 0)
                Case "option e"
                    myColor = RGB(0, 176, 80)
                Case "option F"
                                            
            End Select

            .ForeColor.RGB = myColor

        End With

    Next
    Call AddDataLabels
    
Done:
Exit Sub
Er:
MsgBox "No Data Defined or Data Error for Markers and Labels" & vbNewLine & vbNewLine & "Please be sure the Option Data is populated"
End Sub