Project VBA - using find and select to navigate project views and change colours

For anyone who is used to the power of using VBA to manipulate the look of an Excel sheet then you are going to be a little frustrated with what you can do with VBA in Project.  You cannot for example uses the Task or Resource objects for finding the cell in a view and manipulating that cell to change its colour.  If you want to change the colour of a cell then the only way is to use Select and Find methods to change the ActiveCell and apply font colour changes to the Cell object.  In other words, the Cell object can only be used on the ActiveCell which will appear a bit limiting.

Using the Find method

To illustrated this, in the following example I have some external dependencies in my Project plan that all have task names with DEP: prepended on them.  I can use the Find method to find the row in the current view where the text "DEP: " is found.  To so this, I am first going to call the ViewApply method to make sure that the Gantt view is displayed.

ViewApply Name:="Gantt Chart"

Find Field:="Name", Test:="contains", Value:="DEP:"

We can then use the FontColor method to set the colour.  In this case purple.  If you want to set a specific colour rather than a standard colour you can use FontColorEx and provide an RGB value.  We can also use the Font32Ex method to change the font to bold.

ViewApply Name:="Gantt Chart"

Find Field:="Name", Test:="contains", Value:="DEP:"

ActiveCell.FontColor = pjPurple
Application.Font32Ex Bold:=True

Notice here that FontColor applies to the Cell object whereas the Font32Ex has to be called via the Application object.  You can also change the font size, style, cell background pattern and colour using Font32Ex.

The limitation of the above code is that when I have multiple dependencies in my project that I want to colour them all it is difficult to find them using the Find method.  The Find method will search from the current ActiveCell but I don't know what row number I am on and there is no way of finding it.  I also can't rely on the ID field to know the row number as that could change.  For example when I apply a filter or if I change by Gantt task view to be ordered by another field.

Currently if I looped this code the Find method will start find the same dependency each time because after it finds it the first time, it will start from that position and the next iteration will find the same line again.

Using Select methods

There are several different select methods that are available that can be used to change the ActiveCell to be a different selection.  We can move both relative to the current position or to absolute row numbers.

For example, I can use the following to move the ActiveCell down 1 row relative to where we are.

' move down one row
SelectTaskField Row:=1, Column:="Name", RowRelative:=True

When we then run the Find method again it will find and colour the next dependency.  How do we know when we have found them all?  This is not easy as the Find method will keep loop back to the top of the view and keep searching and we don't know which row number we are on.

To overcome this and complete the code, we can store the task ID of the first Dependency we find with the above code and then store it.  If we increment the line number after each find with the above select task and then put this in a loop, when we get back to our task ID we know we have been all the way through the view.

Dim firstTask As Long

firstTask = 0

ViewApply Name:="Gantt Chart"
    
Do While Find(Field:="Name", Test:="contains", Value:="DEP:")
    
    ' Record if we are the first task ane exit if we have looped
    If firstTask = 0 Then
        firstTask = ActiveCell.Task.ID
    ElseIf firstTask = ActiveCell.Task.ID Then
        Exit Sub
    End If
    
    ActiveCell.FontColor = pjPurple
    Application.Font32Ex Bold:=True
    
    ' move down one row
    SelectTaskField Row:=1, Column:="Name", RowRelative:=True
 Loop

Further Enhancements

There are a lot of different Select type functions that can be used to select a cell, a range, a column, a row or a field.  We could can modify the above code to select the whole row and then colour that rather than just the task name.

Note: when using colours in Project the RGB value is reversed from normal HTML style RGB values.  That is, red is the last byte not the first.  So lavender rgb(230,230,250) is called in Project as hexadecimal &HFAE6E6

In the below code, I uses the SelectRow method to select the whole row that the Find method found and then use the Font32Ex method on the Application object to set the text and background colour using reverse RGB hexadecimal values.

' Select all of the current row
SelectRow RowRelative:=True, Add:=True

' Colour our row purple and make the text bold
Application.Font32Ex Bold:=True, Color:=&H82004B, CellColor:=&HFAE6E6

Some limitations

This macro does the job but there are a couple of limitations.  First, the screen will flicker is the macro runs through the view and changes the colours.  There is nothing we can do about this as pointed out in the beginning, in Project you can only change the cell colours through the interface view.

Secondly, if you have summary tasks collapsed then this code will only work on the visible rows in the view.  So if you have some dependencies in this manner that are hidden under collapsed summary tasks then this code will never get to them.

Download Full File

The full file for the code below can be downloaded from here in a Microsoft Project file with the VBA Macros already setup and ready to use. (It uses a proof of work formula before opening):

MSP VBA - Part 5 - Using Select-Find-Colour in Projects

About Me

If you like this post then please comment below.  Please also make a small contribution so that I can post more code snippets and samples here.

Bitcoin:      1MhaZvTqftdebwNihisrAbz22zaSG6tFcN

Ethereum:  0xe69b176A1A5492f19E3595389EA094C732723b69