Software

Create your own conditional formatting in MS Project

If a task is past its deadline, would you like that information to automatically appear in bold red letters in Microsoft Project? Here's how to use Visual Basic to create a simple macro to perform that, and other, nifty tricks.


Project 2000 has a host of features that you can use to format your project plans and control their attributes. But one feature that is missing in Project 2000 is the ability to conditionally format text in task lines. This powerful feature, which is included in Excel, assigns different colors to the task line to provide visual clues that indicate the current status of each task.

Project 2000 users don’t need to work without this feature, however. This article covers the basics on how you can use Visual Basic to easily create a macro that will place conditional formatting on your task list.

The macro will convert the task text from black and white display (above) to color (below).




A macro to color the task text
Here is a macro that will color the task text based on the Task Cost field value. It uses the Select…Case statement to evaluate the Cost field. Based on certain thresholds, it will tell Project what color to make the task.


Sub SelectTaskRows()
Dim tskT As Task
 
For Each tskT In ActiveProject.Tasks
    Select Case tskT.Cost
        Case 1 To 500
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjGreen
        Case 501 To 2000
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjBlue
        Case 2001 To 5000
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjFuchsia
        Case Is > 5000
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjRed
    End Select
Next tskT
 
End Sub

The Select…Case statement is perfect for this kind of macro because it allows the user to specify a field and then to specify different criteria. The result is that certain code will run depending upon which of the criteria the field meets.

The line that reads “Select Case tskT.Cost” is setting up which field the rest of the statement will be evaluating. Next, there are a series of four Case statements. Each of these statements lays out criteria against which the value of the Cost field should be compared. If the Cost field meets the criteria, then the code on the lines just below that Case will be run. In this way, we can give our Cost thresholds and specify the colors we want for each cost level.
Would you like to receive more advice and ideas about MS Project? Sign up for TechRepublic’s MS Project TechMail now to receive Brian Kennemer’s daily tipsheet.
This basic concept is very flexible. We could customize the macro to evaluate any field and format the text according to that field’s values. We could change the first line of the Select…Case to evaluate Percent Complete rather than Cost. Then we would set up the other Cases below that and specify the formatting we wanted.

The Font method can communicate deadlines
The Font method used in the above macro takes several arguments that control the font properties of the specified text. While the Font method can accept several arguments that allow you to control many aspects of the font, the above macro uses only the color argument. You can also specify bold, italics, font name, and font size. These other arguments may be useful when you design your own formatting macros. There are as many possibilities for what criteria to use for the formatting as there are project managers in the world.

For example, inserting this For…Next loop into the above code in place of the existing loop will format the task text based on the SV (Schedule Variance). This will provide a status report and tell you how far behind or ahead a task is compared with its own baseline.
For Each tskT In ActiveProject.Tasks
    Select Case tskT.SV
        Case Is < 0
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjRed, Bold:=True
        Case Is = 0
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjBlue
        Case Is > 0
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjGreen, Bold:=True
    End Select
Next tskT
 

Any task that has a negative SV is considered behind and will be colored red. Any task that has a positive SV is considered ahead of schedule and is colored green. Zero SV Tasks are right on schedule and colored blue.

Location by color
You may also format the tasks based on the contents of a text field. Perhaps you have a text field that lets you know the location assigned to a task. Is the task taking place in the Washington office or in New York? You will know the answer instantly by looking at the color rather than checking the value of the text field.



The For…Next loop to create this would look like the following:
 
For Each tskT In ActiveProject.Tasks
    Select Case tskT.Text1
        Case "New York"
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjTeal
        Case "California"
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjBlue
        Case "Washington"
            SelectRow Row:=tskT.ID, RowRelative:=False
            Font Color:=pjGreen
        Case "Florida"
            SelectRow Row:=tskT.ID, RowRelative:=False
           Font Color:=pjRed
    End Select
Next tskT
 

Adapting these samples for your own uses is easy. On the line that reads “Select Case tskT…” just insert the field you want to test after the period where SV, Text1, and Cost are in the above samples. Then, you need to adjust the condition that appears just after each of the “Case” statements that are below the Select…Case line. Lastly, you need to edit the Font line so that the formatting you want will be applied for each of the given cases.
Do you have questions about Microsoft Project? Post a comment to this article or send an e-mail to Brian. He can't answer every inquiry, but he will write articles about the topics and questions that readers ask about most frequently.

Editor's Picks