CODEWITHSUNDEEP
excel
dgj918
dgj918

Reputation: 3

Excel Stacked Bar Chart Different Colors Based on Labeling

I am trying to create a Yamazumi chart in Excel that displays the elements of a job by stacking the timing of the elements on top of each other on a stacked bar graph. I want to be able to color the elements based on a label instead of the rainbow of colors I am getting now. For example when it has VA I would like all elements with VA to be green in the graph. This is an example of the data in excel.

walk and load cart NVA 6.35 Excel Data

Excel Data

I have tried this VBA code but does not seem to work. I might not fully understand how VBA is using the variables. Specifically, the seriesArray variable is throwing an error. Any help would be appreciated. 'code' Sub color_chart()

Dim pointIterator As Integer, _
    seriesArray() As Variant

seriesArray = Worksheets("Chart1").ChartObjects(1).Chart.SeriesCollection(1).Values

    For pointIterator = 1 To UBound(seriesArray)

       If InStr(seriesArray(pointIterator), "VA") > 0 Then
           ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
           Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
           RGB(0, 255, 0)
       ElseIf InStr(seriesArray(pointIterator), "NVA") > 0 Then
           ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
           Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
           RGB(255, 0, 0)
       Else
           ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
           Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
           RGB(235, 235, 235)
       End If

Next pointIterator

End Sub 'code'

Upvotes: 0

Views: 1670

Answers (1)

Neal
Neal

Reputation: 3552

As far as I know you have two choices when faced with this generally:

  • Make new 'virtual' series to add to your charts, with blanks anywhere that doesn't correspond to your data. Use a lookup function (like index/match) to create additional columns corresponding to your categories ('NVA', 'VA', 'Wate'). So, if your data screenshot is in columns A, B,C and D, add a formula to column E:

    =IF(B2="NVA",C2,"")

    and replace "NVA" for each other column. Unfortunately, this doesn't appear to work for the graph types you've chosen (stacked bar) in the way you want it to because of other ways that it handles series. It does work for other graph types (here's an example)

  • Use VBA. 

    .Chart.SeriesCollection([myseries]).Interior.Color = RGB(155, 187, 89)

Upvotes: 1

Related Questions