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

Dave-

New Member
Joined
Nov 8, 2013
Messages
14
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
Thursday09 January 201410
Friday10 January 201420
Saturday11 January 201415
Sunday12 January 201414
Monday13 January 201413
Tuesday14 January 201416
Wednesday15 January 201422
Thursday16 January 201425
Friday17 January 201426
Saturday18 January 201457
Sunday19 January 201410
Monday20 January 201433

<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>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Does this work for you?

Code:
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
 
Upvote 0
Does this work for you?

Code:
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
 
Upvote 0
Does this work for you?

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top