VBA Code for Excel 2010 - macro for cell colour represented within bar chart (rows)

[Dave-]

Hi All!

Would appreciate any help or advice anyone could pass on - would be highly appreciated! I have tried to attempt to solve this myself but I have no VBA knowledge and have not found anything on the internet.

What I'm trying to do is create a macro which takes cell (row) colour from a dataset and make the colours the same within a bar chart etc. I have found some coding but it takes the very first cell within a range and locks it - what I need is for the colour to change on certain rows (i.e. weekend data - yellow, weekday - blue).

The coding

I have found is as follows:

• 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
  
• MySeries.MarkerBackgroundColorIndex = SourceRangeColor
  
• MySeries.MarkerForegroundColorIndex = SourceRangeColor
  
• 
  
• '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
  

X Thursday 09 January 2014 10 Friday 10 January 2014 20 Saturday 11 January 2014 15 Sunday 12 January 2014 14 Monday 13 January 2014 13 Tuesday 14 January 2014 16 Wednesday 15 January 2014 22 Thursday 16 January 2014 25 Friday 17 January 2014 26 Saturday 18 January 2014 57 Sunday 19 January 2014 10 Monday 20 January 2014 33 <colgroup><col><col><col></colgroup><tbody> </tbody>
I need the weekends within the bar chart to be one colour (represented by whatever colour is in the cell, the weekdays by another colour. Hope this make some sense - thank you to anyone who could help me - huge thanks!

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

Does this work for you?

Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim i As Long
    Dim FormulaSplit As Variant
    Dim SourceRange As Range
    Dim Cell 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
'           Reset Points counter
            i = 0
'           Get Source Data Range for the target series
            FormulaSplit = Split(MySeries.Formula, ",")
'           Capture the source range
            Set SourceRange = Range(FormulaSplit(2))
'           Loop around the cells in Sourcerange
            For Each Cell In SourceRange
'               Get the cell's color
                SourceRangeColor = Cell.Interior.Color
'               Increment Points counter
                i = i + 1
                With MySeries.Points(i)
                    On Error Resume Next
'                   Coloring for Excel 2003
                    .Interior.Color = SourceRangeColor
                    .Border.Color = SourceRangeColor
                    .MarkerBackgroundColorIndex = SourceRangeColor
                    .MarkerForegroundColorIndex = SourceRangeColor
'                   Coloring for Excel 2007 and 2010
                    .MarkerBackgroundColor = SourceRangeColor
                    .MarkerForegroundColor = SourceRangeColor
                    .Format.Line.ForeColor.RGB = SourceRangeColor
                    .Format.Line.BackColor.RGB = SourceRangeColor
                    .Format.Fill.ForeColor.RGB = SourceRangeColor
                    On Error GoTo 0
                End With
            Next Cell
        Next MySeries
    Next oChart
End Sub

 

[Dave-]

Does this work for you?

Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim i As Long
    Dim FormulaSplit As Variant
    Dim SourceRange As Range
    Dim Cell 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
'           Reset Points counter
            i = 0
'           Get Source Data Range for the target series
            FormulaSplit = Split(MySeries.Formula, ",")
'           Capture the source range
            Set SourceRange = Range(FormulaSplit(2))
'           Loop around the cells in Sourcerange
            For Each Cell In SourceRange
'               Get the cell's color
                SourceRangeColor = Cell.Interior.Color
'               Increment Points counter
                i = i + 1
                With MySeries.Points(i)
                    On Error Resume Next
'                   Coloring for Excel 2003
                    .Interior.Color = SourceRangeColor
                    .Border.Color = SourceRangeColor
                    .MarkerBackgroundColorIndex = SourceRangeColor
                    .MarkerForegroundColorIndex = SourceRangeColor
'                   Coloring for Excel 2007 and 2010
                    .MarkerBackgroundColor = SourceRangeColor
                    .MarkerForegroundColor = SourceRangeColor
                    .Format.Line.ForeColor.RGB = SourceRangeColor
                    .Format.Line.BackColor.RGB = SourceRangeColor
                    .Format.Fill.ForeColor.RGB = SourceRangeColor
                    On Error GoTo 0
                End With
            Next Cell
        Next MySeries
    Next oChart
End Sub

Hi Andrew,

I'll try this at work tomorrow - thank you so much for your time and response - very much appreciated!

Thanks again!
Dave

 

[Dave-]

Does this work for you?

Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim i As Long
    Dim FormulaSplit As Variant
    Dim SourceRange As Range
    Dim Cell 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
'           Reset Points counter
            i = 0
'           Get Source Data Range for the target series
            FormulaSplit = Split(MySeries.Formula, ",")
'           Capture the source range
            Set SourceRange = Range(FormulaSplit(2))
'           Loop around the cells in Sourcerange
            For Each Cell In SourceRange
'               Get the cell's color
                SourceRangeColor = Cell.Interior.Color
'               Increment Points counter
                i = i + 1
                With MySeries.Points(i)
                    On Error Resume Next
'                   Coloring for Excel 2003
                    .Interior.Color = SourceRangeColor
                    .Border.Color = SourceRangeColor
                    .MarkerBackgroundColorIndex = SourceRangeColor
                    .MarkerForegroundColorIndex = SourceRangeColor
'                   Coloring for Excel 2007 and 2010
                    .MarkerBackgroundColor = SourceRangeColor
                    .MarkerForegroundColor = SourceRangeColor
                    .Format.Line.ForeColor.RGB = SourceRangeColor
                    .Format.Line.BackColor.RGB = SourceRangeColor
                    .Format.Fill.ForeColor.RGB = SourceRangeColor
                    On Error GoTo 0
                End With
            Next Cell
        Next MySeries
    Next oChart
End Sub

Hi Andrew,

Just wanted to thank you for your VBA coding you provided - everything worked great!

Thanks again for your fantastic help - very much appreciated!

Dave