Changing color for Stacked bar chart...

kjicha

New Member
Joined
Feb 29, 2016
Messages
41
I have 4 columns Data with the following data

Category, Description, Start Date, Duration (in days)
ABC ABC - Test1 3/1/2016 80
DEF DEF - Test2 1/1/2014 1117
GHI GHI - Test3 2/5/2015 500
JKL JKL - Test4 8/1/2020 400

I have put this data into a Stacked Bar chart, so it looks like a gantt chart. What I am trying to do is I want the bars for ABC - Test1 to show up as Green if Category equals ABC for that description. Then a different color for the next one and so on. I can't seem to figure out how to have the bar colors change automatically. I will take any help I can get, thank you in advance.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,795
Code:
Option Explicit

Sub ChangeDataPointColor()

    Dim ser As Series
    Dim pt As Point
    Dim sTriggerSeries
    Dim sTriggerCategory
    Dim bShowingValue As Boolean
    Dim bShowingCatName As Boolean
    Dim bShowingSeriesName As Boolean
    
    sTriggerSeries = "ABC - Test1"
    sTriggerCategory = "ABC"
    
    For Each ser In ActiveChart.SeriesCollection
        If ser.Name = sTriggerSeries Then
            'Save Data Label Config
            If ser.HasDataLabels Then
                bShowingValue = ser.DataLabels.ShowValue
                bShowingCatName = ser.DataLabels.ShowCategoryName
                bShowingSeriesName = ser.ShowSeriesName
            Else
                ser.ApplyDataLabels
            End If
            
            'Set Data Labels as required
            ser.DataLabels.ShowValue = False
            ser.DataLabels.ShowCategoryName = True
            ser.ShowSeriesName = False
            
            'Check Point Category names
            For Each pt In ser.Points
                If pt.DataLabel.Text = sTriggerCategory Then
                    pt.Format.Fill.ForeColor.RGB = rgbGreen
                End If
            Next
            
            'Restore Data Labed Config
            ser.DataLabels.ShowValue = bShowingValue
            ser.DataLabels.ShowCategoryName = bShowingCatName
            ser.ShowSeriesName = bShowingSeriesName
            
        End If
    Next
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,225
Messages
5,443,194
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top