HELP ME! VBA to automatically format charts

[roomgrey]

Hello guys,

I'm a total noob in VBA. Please help me to merge this two solution into one below. The 1st is a loop that colors the chart's lines based on the 1st cells color. The 2nd can change the line style based on the text written in a cell (currently D4). What I'd like to have is a macro that clones the color of the 1st cell in the series for the chart line color and clones the style of the same series based on a cell text at the beginnig or at the end of the series.
Here are the macros:

1st:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long

'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects

'Loop through all series in the target chart
For Each MySeries In oChart.chart.SeriesCollection

'Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, ",")

'Capture the first cell in the source range then trap the color
Set SourceRange = Range(FormulaSplit(2)).Item(1)
SourceRangeColor = SourceRange.Interior.Color

On Error Resume Next
'Coloring for Excel 2003
MySeries.Interior.Color = SourceRangeColor
MySeries.Border.Color = SourceRangeColor

'Coloring for Excel 2007 and 2010
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor

Next MySeries
Next oChart

End Sub</code>
2nd:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_Change(ByVal Target As Range)

Dim dash_style As String
Set Target = Range("d1")
dash_style = Target.Value

With ActiveSheet.ChartObjects("Wykres 1").Chart.SeriesCollection(1).Format.Line
Select Case dash_style
Case "LineDash": .DashStyle = msoLineDash
Case "LineDashDot": .DashStyle = msoLineDashDot
Case "LineDashDotDot": .DashStyle = msoLineDashDotDot
Case "LineLongDash": .DashStyle = msoLineLongDash
Case "LineLongDashDot": .DashStyle = msoLineLongDashDot
Case "LineRoundDot": .DashStyle = msoLineRoundDot
Case "LineSolid": .DashStyle = msoLineSolid
Case "LineSquareDot": .DashStyle = msoLineSquareDot
Case Else:
End Select
End With
Set Target = Nothing
End Sub</code>
thanks a lot,
Norbert