Djani
05-13-2016, 02:53 AM
Dear all,
I have a properly working code that I found on the internet which automatically gives a specific series a color (http://datapigtechnologies.com/blog/index.php/automatically-label-first-and-last-chart-points/).
However, how can I modify the macro to have the lines without the borderline/fill?
This is the code:
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 2007 and 2010
MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
Next MySeries
Next oChart
End Sub
Can any of you lend me a hand?
Yours sincerely,
Djani
I have a properly working code that I found on the internet which automatically gives a specific series a color (http://datapigtechnologies.com/blog/index.php/automatically-label-first-and-last-chart-points/).
However, how can I modify the macro to have the lines without the borderline/fill?
This is the code:
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 2007 and 2010
MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
Next MySeries
Next oChart
End Sub
Can any of you lend me a hand?
Yours sincerely,
Djani