07 Jul How to automatically change the colour of a task name

<Leffe Blond>

This requirement came from a customer more as a general query rather than a full demand, but I thought it should be an interesting enough problem to invest some time in.

Changing the font, colour, or any of the other options available under Format | Text Styles can only be done automatically for a certain set of (Microsoft determined) pre-defined tasks. The only option left for the PM is either to use the “Marked” column, or to resort to code/VBA.

Customer request

So, the original request was to change the colour of the text based on the % complete. This is relatively simple, we can accomplish this using the following statement.

Loop through all the tasks, and for each task that is not a summary or blank task, do the following….

If Tsk.PercentComplete = 100 Then
SelectRow Tsk.ID, RowRelative:=False
SelectTaskField Row:=Tsk.ID, RowRelative:=False, Column:=”Name”
Font Color:=pjGray
Else
SelectRow Tsk.ID, RowRelative:=False
SelectTaskField Row:=Tsk.ID, RowRelative:=False, Column:=”Name”
Font Color:=pjBlack
End If

So far so good, but you’ll notice that if you run this then two things happen. Firstly, the screen flickers during the update. This can be switched on/off by using the following statement at the beginning of the code

Application.ScreenUpdating = False

and then

Application.ScreenUpdating = Ture

at the end of the code.

The second issue is that the cursor moves as it loops through the tasks, so it is worth saving the original location of the cursor, and then resetting that when the code as run.

Dim OriginalTsk as Task

Set OriginalTsk = ActiveCell.Task

and then at the end of the code…

EditGoTo ID:=OriginalTsk.ID

I’m no coder, but the above works, and it wouldn’t have worked without the input of both Nico (via the forums whose published code I borrowed) and Catalin Olteanu and Rod Gill who answered specific queries – thank you all.

Enjoy, Ben.