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

D

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




[TABLE="width: 369"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][TABLE="width: 326"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]09 January 2014[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]10 January 2014[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]11 January 2014[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]12 January 2014[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]13 January 2014[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]14 January 2014[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]15 January 2014[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]16 January 2014[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]17 January 2014[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]18 January 2014[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]19 January 2014[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]20 January 2014[/TD]
[TD]33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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!
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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
Andrew Poulsom said:
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
Click to expand...

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
Andrew Poulsom said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

R
  • Question Question
HELP ME! VBA to automatically format charts
Replies
0
Views
971
roomgrey
R
T
  • Question Question
VBA color changer on bar chart-- can you help fix my code
Replies
9
Views
2K
Worf
Worf
A
  • Question Question
VBA Chart Matches Cell color, but Legend Won't Follow
2 3
Replies
20
Views
6K
alexb523
A
G
  • Question Question
Changing the Gradient Fill of an Area Graph - VBA
Replies
0
Views
2K
greekmac7
G
B
  • Question Question
VBA Chart by series color, update to Chart by cell color (stacked Bar chart)
Replies
1
Views
5K
brandon20
B

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

Share this page

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