Conditional Formatting in MS Project 2010 via VBA

This is a bit tricky, as Project doesn’t give the normal hooks for getting access to cell properties, like Excel. Also, the event model for Project is accessed a bit differently.  So, a couple possibilities:

Marked Fields

VBA can set a row as marked, and the style of marked rows can be set via Gantt Chart Tools –> Text Styles –> Item to Change –> Marked Tasks

Once the formatting has been set, it’s simple enough to change a row to marked:


Dim t As Task
For Each t In ActiveProject.Tasks
If t.Cost > 10 Then
t.Marked = True
Else
t.Marked = False
End If
Next

Formatting Individual Cells

However, this colors the entire row, not an individual cell. To shade individual cells, there’s a function named “Font32Ex”. The difficulty with this function is that it works with the selected cell, and doesn’t take a cell reference. To select a cell, we can use “SelectTaskField”:

SelectTaskField Row:=100, Column:=”Cost”, RowRelative:=False

RowRelative is key here, in that the above will select the 100th row, regardless of where the focus was. So, we can put the above in a loop:


Dim i As Integer
For i = 1 To pj.Tasks.Count
Dim t As Task
Set t = pj.Tasks(i)
If t.Cost > 5 Then
SelectTaskField Row:=i, Column:="Cost", RowRelative:=False
Font32Ex CellColor:=62207
Else
SelectTaskField Row:=i, Column:="Cost", RowRelative:=False
Font32Ex CellColor:=-16777216
End If
Next

Note: the color values were obtained just by recording a macro and changing the color in the macro.

The above meets my needs, but perhaps the cell needs to be changed on edit, and not loop through all tasks. If so, check out the technet article that goes over hooking into task level events:

https://msdn.microsoft.com/en-us/library/ee355232%28v=office.12%29.aspx#odc_pj2007_bk_VBAProgrammingProject_UsingTaskEvents

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s