Varying Fills within single Data Series

**cjsec9** · 12-01-2008, 03:29 PM

Good morning,

I have a stacked bar chart that is being used as a Gantt chart. It displays data from different varieties of different fruits (for example, five varieties of peaches, five varieties of nectarines, six varieties of plums, etc). I need to be able to set different fill colors for different fruits. For example, all of the peach varieties should be yellow, all of the nectarine varieties should be red, all of the plum varieties should be purple, etc. Is this possible?

Thanks

**Andy Pope** · 12-01-2008, 03:50 PM

Either create a series for each variation. Setting the values to zero for those items not required at each item.

Or vba code can be used to set Colours

If you need further help post example of your current chart.

**cjsec9** · 12-01-2008, 04:08 PM

Originally Posted by Andy Pope

Either create a series for each variation. Setting the values to zero for those items not required at each item.

Or vba code can be used to set Colours

If you need further help post example of your current chart.

I'm not sure if I would be able to create multiple series and still keep the functionality and format of the chart as I need it. I've attached the Gantt chart tab from my sheet. The chart displays when a particular variety begins and ends harvesting, separated by fruit. I need to provide a further visual separation by having the peach varieties in one color, the nectarine varieties in one color, etc. I control the Gantt display by changing the filters over the "COMM" column (Column G). I prepare a variety of different charts from this master list, such as all fruit from the "EAST" side, or all "WEST" "PEACH", etc.

Thanks

**Andy Pope** · 12-01-2008, 04:53 PM

Try the VBA approach.

You need to add in the names you want to alter and the colour with which to fill the bar.

Code:

Sub ColourGantt()

    Dim objCht As Chart
    Dim vntItem As Variant
    Dim lngIndex As Long
    
    Set objCht = ActiveSheet.ChartObjects(1).Chart
    For Each vntItem In objCht.SeriesCollection(1).XValues
        lngIndex = lngIndex + 1
        Select Case vntItem
        Case "FIRE"
            objCht.SeriesCollection(2).Points(lngIndex).Interior.ColorIndex = 3
        Case "BLAZE"
            objCht.SeriesCollection(2).Points(lngIndex).Interior.ColorIndex = 44
        Case "COT"
            objCht.SeriesCollection(2).Points(lngIndex).Interior.ColorIndex = 24
        Case "PEACH 15"
            objCht.SeriesCollection(2).Points(lngIndex).Interior.ColorIndex = 45
        End Select
    Next
    
End Sub

**cjsec9** · 12-01-2008, 06:04 PM

That code seems to be doing the trick. The only problem I'm having is when I filter my chart data, the coloring doesn't stick quite right. I have to have the VB window open on the side to hit the "Run Sub" button each time I make a change to the graph so the colors can catch up. Is this an issue caused by the way I entered it? Did I perhaps make a mistake when entering the code?

**Andy Pope** · 12-02-2008, 05:25 AM

Try this addition to the sheet, which will update the chart when the sheet calculates.

Right click sheet tab and View Code. Paste in sheet object.

Code:

Private Sub Worksheet_Calculate()
    ColourGantt
End Sub

Revision to colouration code

Code:

Sub ColourGantt()

    Dim objCht As Chart
    Dim vntItem As Variant
    Dim lngIndex As Long
    Dim lngColorIndex As Long
    
    Set objCht = ActiveSheet.ChartObjects(1).Chart
    For Each vntItem In objCht.SeriesCollection(1).XValues
        lngIndex = lngIndex + 1
        Select Case vntItem
        Case "FIRE"
            lngColorIndex = 3
        Case "BLAZE"
            lngColorIndex = 18
        Case "COT"
            lngColorIndex = 24
        Case "PEACH 15"
            lngColorIndex = 34
        Case Else
            lngColorIndex = 53
        End Select
        With objCht.SeriesCollection(2).Points(lngIndex).Interior
            .ColorIndex = lngColorIndex
            .Pattern = xlSolid
        End With
    Next
    
End Sub

**cjsec9** · 12-02-2008, 04:09 PM

The updated color code works, but the refresh code does not. The sheet code is as follows:

Code:

Private Sub Worksheet_Calculate()
    ColourGantt
End Sub
-----------------------------------------
Sub ColourGantt()

    Dim objCht As Chart
    Dim vntItem As Variant
    Dim lngIndex As Long
    Dim lngColorIndex As Long
(et cetera)

Update:
When I filter my data now, and run the Run Sub command, the new color code no longer updates the colors of the bars.

**Andy Pope** · 12-02-2008, 05:49 PM

This works for me.

Thread: Varying Fills within single Data Series

Thread Tools

Display

Varying Fills within single Data Series

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions