Macro to remove marker line and border

[Djani]

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

[PAB]

Hi Djani,

Why don't you try commenting out the lines in the code...



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

...one by one until you get the desired result?

I hope this helps!

[Djani]

Hi PAB,

I'm sorry, but I don't entirely understand that. Could you give me an example?
Isn't it possible to delete all marker lines at once instead of doing it one-by-one?

Yours sincerely,

Djani

[PAB]

Isn't it possible to delete all marker lines at once instead of doing it one-by-one?

When you say "marker lines", do you mean the borders that go round the cells themselves?

[Djani]

Indeed, sorry for the miscommunication. I'm talking about the borders and the filling indeed.

I thought it would be possible to play around with this piece of code:


MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor



I tried to replace "SourceRangeColor" with xlNone or -2, but this results in white borders around the colored lines.

[PAB]

Give this a go Djani,


Sub Clear_Borders_And_Fill()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With

With ActiveSheet.Cells
.Borders.LineStyle = xlNone
.Interior.Pattern = xlNone
End With
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
Range("A1").Select
End Sub


EDIT: Try this on a copy of your WorkBook first, just in case!

I hope this helps!

[SamT]

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
Next MySeries
Next oChart
End Sub

[Djani]

@PAB: Your macro clears the formatting of the entire sheet. It should remove the filling/border of the graph lines!

@SamT: I have tested your macro, but it doesn't change anything!