Mass change of color of Gant chart bar 1

[mystuff]

Is there a way to do a mass change of the Gant bar color based on certain criteria in a task.

For example, I would like to make all of the tasks that start with the letter "a" to have a color of red in the Gant chart. Then all of the tasks that start with "b" another color, etc. (The starting letter identifies the of task this is.)

Thanks.

 

[PDQBach]

Nobody wants to do that! But I suspect you're just looking for a specific solution so you can modify it to fit your specific case.

Here's what I did.

1. You want the first character of the task name? Okay, here's the first character. In Text11 put the formula

Left([Name],1)

(Are you serious?!?!?!? You _really_ want to set the colours based on the first character of the task name?!?!?!)

2. Now, we'll set a Flag field.

In Flag11 put the formula UCase([Text11])="A"
In Flag12 put the formula UCase([Text11])="B"
In Flag13 put the formula UCase([Text11])="C"

You _must_ be kidding. Do you honestly, truly, sincerely, want ... okay, we'll assume you do. Unfortunately there are only 20 flag fields so you'll have to compromise. Some flags will have to have a formula similar to this:

(UCase([Text11])="C") Or (UCase([Text11])="E")

3. Now, finally, (you can't possibly be serious) we'll set the bar colours. (Wait, you can't be serious because there aren't 26 bar colours.)

Format | Bar Styles

You'll have to insert a bunch of lines at the top and label them Task.

For every line you've entered:

(a) In the Appearance column choose a colour and a pattern (you need the pattern because there are only 16 bar colours and you want one colour for each letter of the alphabet)

(b) In the Show for ... Tasks, select a different Flag.

Basically the colour (and pattern) selection work like this: each Gantt line starts at the top of the list and moves down the list until the "Show for ... Tasks" condition is satisfied. Once the condition is satisfied, the bar attributes (colour and pattern) are selected for that bar, the bar is displayed and processing moves to the next task.

(Now, come on, 'fess up: what was the condition you _really_ wanted to test.)

 

[mystuff]

Hi PDQBach,

I must confess, you are right. I do not need to change color based on 1st letter of name. I need to change the color based on the value of another field. I thought it would be easy to post a message based on 1st letter so I didn't have to go into detail about the other field. So, I apologize for you spending the time on showing me how to break out the 1st letter. However, the other info on changing color is what I needed. So thank you very much.

I have one more question ... but this time I'll give you the correct detail, so you don't spend time on something that I don't need.

I have an “Access” database, that I want to import into Project. Each "Project" is associated with a "Tower", and "Bay" with a start and end date. I can load it either from “Access”, or dump the file from “Access” into “Excel” and bring it into “Project” from there. Whichever is easier. In access the data is stored like this:

Project Tower Bay Start End
TORO EIM Bay 1 8/3/04 11/5/04
TORO EIM Bay 2 8/3/04 11/5/04
TORO BSDC Bay 1 8/3/04 11/5/04
TORO BSDC Bay 2 8/3/04 11/5/04
CORN EIM Bay 1 8/3/04 11/26/04
CORN EIM Bay 2 8/3/04 11/26/04
CORN QS Bay 1 8/3/04 11/26/04
CORN QS Bay 3 8/3/04 11/26/04
CORN BSDC Bay 1 8/3/04 11/26/04

OR, I can dump this into Excel something like this: (Data is different, but same idea)

Project Tower Bay Start End
CORN
EIM
Bay 2
8/3/2004 11/26/2004
TORO
EIM
Bay 1
8/3/2004 11/5/2004
8/3/2004 11/5/2004


I want to bring it into Project but I want to Indent Tower and Bay sort of like this: (Again,data is not the same, but fields are the same)

+ TORO
+ EIM
Bay1 8/3/2004 9/3/2004
Bay2 8/3/2004 9/3/2004
+ BSDC
Bay1 8/3/2004 9/3/2004
Bay2 8/3/2004 9/3/2004

+ Corn
+EIM
Bay1 8/3/2004 9/3/2004
Bay2 8/3/2004 9/3/2004
+QS
Bay1 8/3/2004 9/3/2004
Bay3 8/3/2004 9/3/2004
+ BSDC
Bay1 8/3/2004 9/3/2004

Any idea how I would do this?

Thanks!

 

[PDQBach]

I chuckled as I read your first set of requirements.

Now you can chuckle as I tell you how I used brute force to accomplish your second.

I was using a machine that didn't have Access so I used Excel.

1. Create a project plan that looks like the plan you want to end up with. I just used dummy tasks (t1, t2, t3, ...), and I put dummy data into the fields I thought you'd want to use (id, name, outline level, work, duration, start, finish).

2. Click File | Save As ..., select a name for the file, choose a spreadsheet in the "save as type" field and click on Save. This will launch the wizard for data export and you'll get a window with "Selective data" already chosen.

3. Click on the New Map button and you'll get the next screen. Give the map a memorable name and click on the "Tasks" check box (yeah, there are other possibilities ... let's keep it simple for now). This will set the Task Mapping tab to enabled.

4. Click on the Task Mapping tab. Give the Destination Worksheet name a memorable name. In the column labelled "From: Microsoft Project Field" select the various fields you want to export (in other words, select the various fields that, later on, you will be importing). ID and Outline Level are two of the fields you definitely want.

5. Eventually, you'll have all the fields you want. Click on OK. That will take you back a screen where the map you just created will be listed in the map scroll box. Select your map (if it isn't already selected) and click on the Save button.

6. Stay in project and start a new project. Minimize project.

7. Start Exel and open the spreadsheet you just created. That's your template for the column names.

8. Create a spreadsheet with all the information you want to import and give the columns the titles from the template. (It would probably be a good idea to keep the same column sequence but I didn't try checking to see what would happen if I tried using the same names but changed their position in the spreadsheet.) Save this spreadsheet.

9. Back in Project, click on File | Open and open the spreadsheet you just saved. You'll be asked to select a map (and worksheet name?? Can't remember) so select the map (and worksheet name, if necessary) you recently created, click on Open and ... Voila!

10. The reason I had you keep project open is that the map we just created is local to that instance of Project. I believe that if you want to make it persist, you'll need to move it to global.mpt using the organizer function. I didn't have time to check this out.

 

[mystuff]

Hi PDQBatch,


Your suggestion worked perfect! Thanks. I need to ask you one more question. I have four Outline Levels. I would like to change the color of the bar for level 3. How do I do that?

Thanks.

 

[PDQBach]

You'll P2000 or later.

1. Set up Flag1 with a formula.
1a. Insert the Flag1 column
1b. RightMouseButton on the column header Flag1 and click on "Customize Fields ..." which will open a new popup.
1c. Click on the formula button which will open another new popup
1d. Paste the next line into the popup.
[Outline Level]=3
1e. Click on OK
1f. If you want Summary bars to also have a different colour then click on the radio button labelled "Use Formula".

2. Set up the bars for Outline Level 3.
2a. Click on Format | Bar Styles ...
2b. You'll see a row named "Task". Click on the row beneath it and then click on the Insert Row button to open up some space.
2c. In the columns enter the following information:
Name: Level3Bar
Appearance: go to the tab below labelled Bars and click on it to make it active; then select a colour.
Show for ... Tasks: Flag1 (it's one of the fields in the dropdown ... you can only select one of those fields)
Row: 1 (don't change it)
From: Start (don't change it)
To: Finish (don't change it)
Click on OK

If you move the line you just entered up and down (cut it and paste it in different locations) you'll see how Project logically processes the conditions. It takes quite a few words to explain the logic so it's best if you just play around.

Note: (kinda obvious) if you use a different flag in step 1, then you have to use the same flag in step 2.

 

[mystuff]

You are amazing! That worked great! I think I am getting the hang of flags now. I have another question (sorry -- I will try not to bother you after this.) Is it possible to change the background color of the left side of the gant chart (the side that lists all of your tasks.) My boss would like to change the background color of the text area everytime their is a change in Outline Level 3. (We have 4 Outline levels). The reason for this is that our Gant chart has quite a few columns, and it is difficult to follow across. If we alternate backgournd colors (or maybe add a line seperator) everytime Outline level 3 changes, it will be easier to read across. I found where I can change the text, but I could not find a way to change the background color. (It is white now). I also was able to add a grid line on the Gant side everytime Level 3 changes. Thanks.

 

[PDQBach]

You can change the colour of the text but not the colour of the background. Sorry.

I believe MS Project staff discuss it periodically but it's not high on their "to do" list.

 

[mystuff]

OK, I must be going crazy. In my previous post I told you that I was able to add a grid line on the Gant side everytime Level 3 changes. Well, now I can't figure out how I did that. I have a flag set to [Outline Level] = 3. And it shows "Yes" everytime my level 3 changes. But I can't figure out how I made the grid line appear when Level 3 changes. Can you help?

Thanks!

 

[PDQBach]

Gridline ... do you mean the vertical lines on the chart? I've never seen a way to display them on some lines and not others. If you've found a way, please let me know.

Do you mean the horizontal lines? I know how to display them for every, every 2nd, every 3rd, etc., line but not how to do it for specific cases. Again, if you've found a way, please let me know.

I think we're now reduced to looking at the task bars on the Gantt chart. Perhaps you mean that you were able to display a different coloured Gantt bar. If that's the case then try this:

(A) Set up a bar style

1. Format | Bar Styles...
2. Click on the line below the line labled "Task"
3. Click on Insert Row
4. For "Appearance" click on the Bars tab. In the "middle bar" section, select a shape (I chose a medium horizonal line -- the third item in the selections); select a nice colour (I chose light blue).
5. For "Show For ... Tasks" select "Flag3"
6. Click on OK.

(B) Set up the condition so the bar style will be selected
(from memory because I don't have P2000+ on this machine)
1. Display the Flag3 column
2. RightMouseButton on the column header and select "Customize..."
3. Click on the "Formula button"
4. In the formula field type "[Outline Level]=3" (no quotes)
5. Click on OK or whatever until all the popups are closed.

Now ... whenever a task moves to Outline Level 3 then the second bar (see step A) should overlay the standard task bar on the Gantt chart.

If this isn't what you wanted, just ask again with more explanation.