Gantt Chart - Data Bars not all correct color for each project

Webbers · Feb 22nd 2019

Hi all!

Thanks in advance for your help. I have designed a project tracker with a Gantt chart in Excel. Now I have one issue that I simply cannot figure out how to correct, and I have searched the forum here as well as I have Google'd for an answer without success. Now in my example attached, there are 4 projects listed (1-4). And each project has 4 tasks associated with it. There are multiple colors on my chart, and I like this visual as it makes everything easier to read (my real template has 67 projects that must be tracked, so this is very important). Okay now each project and the associated 4 tasks should be in the same color. I have highlighted in yellow on my spreadsheet example the task(s) that show an incorrect color based on the project the task is associated with.

For example
[TABLE="border: 1, cellpadding: 1"]

[tr]

[td]

Row #

[/td]

[td]

Project/Task Name

[/td]

[td]

Notes

[/td]

[/tr]

[tr]

[td]

10

[/td]

[td]

1.4 Implementation

[/td]

[td]

The chart shows this bar in green, however, as it is part of project 1, it should b e blue like the rest of project 1.

[/td]

[/tr]

[tr]

[td]

14

[/td]

[td]

2.2 Development

[/td]

[td]

These 3 tasks are part of project 2, so the data bars should be in green. However they show in grey.

[/td]

[/tr]

[tr]

[td]

15

[/td]

[td]

2.3 Testing

[/td]

[td]

These 3 tasks are part of project 2, so the data bars should be in green. However they show in grey.

[/td]

[/tr]

[tr]

[td]

16

[/td]

[td]

2.4 Implementation

[/td]

[td]

These 3 tasks are part of project 2, so the data bars should be in green. However they show in grey.

[/td]

[/tr]

[tr]

[td]

22

[/td]

[td]

3.4 Implementation

[/td]

[td]

This task is part of project 3, so the data bars should be in purple. However it shows in red.

[/td]

[/tr]

[/TABLE]

Now, beginning with project 4, everything shows in red. Ideally I would like to be able to "rotate" though a group of colors. i do not expect to be able to use unique colors for each project. Ideally I would like to be able to have this set so it rotates though 5 or 6 colors. And please tell me HOW to do this in my master file where I have 67 projects listed already.

Thanks again!

**KjBox** · Feb 22nd 2019

Try the attached file.

I have added a sheet named "Colors" which has an excel built-in table. The table lists your project number in column 1 and the color to be used in the chart for that project in column 2. You can add to this table for every project in your actual file, and assign a color for every project (this way you can indeed have a different color for each project). You can edit any color at any time and run the code, there is no need to change anything in the code.

Click the button on the Project sheet to update the chart colors.

Code assigned to the button is

Code

Sub btnUpdate_Click()
    Dim Area, i As Long, ii As Integer, iii As Integer
    ii = -4
    
    Application.ScreenUpdating = 0
    With ActiveSheet
        For Each Area In .Columns(1).SpecialCells(2).Areas
            i = i + 1: ii = ii + 5
            For iii = ii To ii + 5
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.FullSeriesCollection(2).Points(iii + i - 1).Select
                Selection.Format.Fill.ForeColor.RGB = _
                Sheets("Colors").ListObjects(1).ListRows(i).Range.Columns(2).Interior.Color
            Next
        Next
        Application.Goto .[a1]
    End With
                
            
End Sub

Display More

**KjBox** · Feb 22nd 2019

Strange, it works perfectly for me.

Can you use f8 to step through the code and let me know the code line that triggers the error.

**KjBox** · Feb 22nd 2019

Try this version

Webbers · Feb 22nd 2019

KjBox,

Yes it did work properly. I am sorry about the confusion. It was my fault. I did not even see your button on the page. As a normal habit I went to Run the macro I the traditional sense. I do have one additional request I would like to add these lit of colors to my tab named "Codes", and I can put the button there too... I would like to the VBA to be aimed at the sheet called "Project Status" rather than the active sheet. there will be many people in this workbook, and I want to keep them away from this if possible. The color tab resided in columns A & B of my Codes tab. Can you adjust the code to account for it being on Codes rather than the colors tab, and also set it to be not require being the active sheet but specifically the Project Status tab where the chart is. And one last thing. Can this code be adjusted to run like a regular macro rather than the button? I will be the only one using this macro to manage the project colors.

Thanks so very much!!!!

**KjBox** · Feb 22nd 2019

Try this. You can run the macro by either viewing all macros, selecting "UpdateColors" and clicking Run, or by using the shortcut I assigned to it (ctrl+shift+U). When using the shortcut you do not need to open the macros dialogue at all.

**KjBox** · Feb 25th 2019

Is that using the file I attached? It works perfectly for me.

**KjBox** · Feb 25th 2019

It is exactly the same code as was working for you previously, just being triggered manually rather than via a button.

Webbers · Feb 26th 2019

Yes, nd when I use it on the "sample" file I provided, it works perfectly. However when I use it on my real file, which contains additional tabs and more projects than just the 8 indicated in the example, it does not work at all. I don not understand. This sample file was made from a copy of the original and them I removed the extra tabs, which are not relevant. The only thing that is different is that on the Project Status tab, all that data is formulas generated from other tabs within the workbook. Would that make a difference? That never dawned on me, so forgive me if that is the issue. However, other than that... identical.

**KjBox** · Feb 27th 2019

You really need to attach your actual workbook. If the data is sensitive the change it but keep all formulas and worksheet structures the same as the original.

Webbers · Mar 7th 2019

KjBox,

Sorry for the delay, I have been out of town. As I stated, I made a duplicate of my original file. Trimmed the quantity and converted the sensitive data, otherwise it is identical. I do not see how the end result would be any different base on what I did.

Gantt Chart - Data Bars not all correct color for each project

Participate now!

Share