Formatting Plots Series Based On User Parameters

[thegrubixcube]

Hi all,

I have been googling my issue for hours, and I can't find any solutions online, so I though I'd ask the experts. I am trying to format about 15 plots with several data series each, so I want to write a macro to automate the process rather than formatting 100+ series by hand.

I have created a separate worksheet (sample shown below) where a user can enter formatting parameters for each series, however I am having difficulties translating these specifications in the macro.

The parameters I want the user to set are Marker Style, Marker Fill, Line Color, and Line Style. In Column A, I have the series names listed. Columns B-E have the parameters listed respectively:

Run Name Marker Style   Marker Fill	     Line Color     	     Line Style
ACE-01 	   1	      msoThemeColorAccent1	msoThemeColorAccent1	1
ACE-02 	   2	      msoThemeColorAccent1	msoThemeColorAccent1	1
ACE-03 	   1	      msoThemeColorAccent2	msoThemeColorAccent2	1

In this case I thought it would be easier just to designate Marker Fill and Line Color as MSO themes, that way I could use a string variable and not have to translate numbers into colors. In my code I want to set a variable called "parameter" as a string, start at cell B2, read across a row, and format a series according to user defined values, then proceed to the next series. For example, the ACE-01 run would have square Marker Style (designated by 1), with blue Marker Fill and Line Color (designated by msoThemeColorAccent1), and a solid line (designated by a 1 (see code for loop)).

For some reason though, I just can't get the syntax right to define my parameter variable. All I want is it to be a string of a particular cell value, then update to the next cell value by being offset in a loop. I don't know if this is possible, but it seems like it should be. Does anyone know of how I might be able to accomplish this, or perhaps an alternate method? I have included my code below.

The error occurs in the Set parameter = Range("B2").Data line towards the beginning. So this is obviously wrong, but I don't know how to resolve it. If I can get something to work for one plot I can easily loop it for the others.

I am using Excel 2010, and I used the record macro feature as a starting point for my plot formatting code, so it's possible that some of it is not optimal.

Thanks for reading! Sorry for the lengthy post.

Sub FormatGraphs()
    
    Dim runs As Integer
    ' Declares 'runs' as an integer to represent number of times loop will run and which rows of data to select.
    
    Dim parameter As String
    ' Declares 'parameter' as a range to determine series formatting.
    
    Sheets("Format Data").Activate
    ' Sets active worksheet to "Format Data" sheet
        
    Set parameter = Range("B2").Data
    ' Sets initial formatting parameter to marker style
    
    runs = Worksheets("Format Data").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
    ' Initializes number of runs as number of rows with data in Column A -1 (Run Name)


    For I = 1 To runs
    ' Loop to format series in plot. Will format number of series corresponding to value of "runs" calculated previously.
        
        Sheets("Viable").Select
        ' Sets active worksheet
        
        ActiveChart.SeriesCollection(I).Select
        ' Selects first series on worksheet
        
        With Selection
            .MarkerStyle = -4115
            .MarkerSize = 7
        End With
        Selection.MarkerStyle = parameter
        ' Sets marker style based on user specification
        
        Set parameter = parameter.Offset(0, 1)
        ' Shifts parameter location to Marker Fill category
        
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = parameter
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Solid
        End With
        ' Sets marker color based on user specification
        
        Set parameter = parameter.Offset(0, 1)
        ' Shifts parameter location to Line Color category
        
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = parameter
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
        End With
        ' Sets line color based on user specification
        
        Set parameter = parameter.Offset(0, 1)
        ' Shifts parameter location to Line Style category
        
        If parameter = 0 Then
            With Selection.Format.Line
                .Visible = msoTrue
                .DashStyle = msoLineDash
            End With
        End If
        ' Sets line style based on user specification (0 = dashed, 1 = solid)
        
       Set parameter = parameter.Offset(1, -3)
       ' Offsets formatting to next series' specifications
        
    Next I
    
    
End Sub