VBA Macro for MS Project (Color Coded WBS)

Question

Hi There,

I'm trying to create a Color Coded MS Project Schedule to looks similar to my P6 Schedule.
But I guess that is only possible using VBA Macros, right? Or there is any other way?

Is there any code that I could use to do this?

Color:

Project Summary Task - RGB : 255 51 47

WBS Level 01 - RGB : 0 91 0
WBS Level 02 - RGB : 67 135 135

WBS Level 03 - RGB : 255 184 113

WBS Level 04 - RGB : 215 235 255
WBS Level 05 - RGB : 0 128 192

WBS Level 06 - RGB : 242 197 57

Activity Level - White (Typical)

Answer 1

ThiagoBM2021,

Here ya go. I couldn't get your RBG colors to work, but this should give you a good start.

John

Sub HighlightSummaries()

SelectAll

EditClearFormats

For i = 1 To 7

    FilterEdit Name:="OLX", taskfilter:=True, Create:=True, OverwriteExisting:=True, \_

        FieldName:="summary", test:="equals", Value:="yes", ShowInMenu:=False

    FilterEdit Name:="OLX", taskfilter:=True, Operation:="and", \_

        NewFieldName:="outline level", test:="equals", Value:=CStr(i)

    FilterApply Name:="OLX"

    SelectAll

    If ActiveSelection > 0 Then

        If i = 1 Then Font32Ex Color:=16777215, CellColor:=11892014

        If i = 2 Then Font32Ex CellColor:=8630772  'salmon

        If i = 3 Then Font32Ex CellColor:=15652797 'light blue

        If i = 4 Then Font32Ex Color:=16777215, CellColor:=9851951 'dark blue

        If i = 5 Then Font32Ex CellColor:=49407 'light grey

        If i = 6 Then Font32Ex CellColor:=12566463  'purple

        If i = 7 Then Font32Ex Color:=16777215, CellColor:=10498160

    Else

        Exit For

    End If

Next i

FilterApply Name:="all tasks"

End Sub

Answer 2

Sure, it's done :)

Regarding the colour, I just saw that adding the colour code as " Color:=RGB(0, 91, 0)" makes it simple to relate to the colour required using the RGB colours.

The "Cherry on the top of this cake" would be if this Macro could run automatically every time that I add a new task or summary task on the chedule. Is there a way to do this?

Answer 3

ThiagoBM2021,

I'm glad you found a simple solution to getting the RBG color code you want.

Yes, the macro can be set up as an Event macro perhaps using the Change Event but it is more complex to set up as it involves creating a Class module. I won't write that for you but here is a reference if you want to try it yourself.

https://docs.microsoft.com/en-us/office/vba/project/concepts/using-events-with-application-and-project-objects

I just thought of a couple other options. The above is the "cherry" option, here is the "blueberry" option. It is quite simple to set up the macro I gave you as an Open or Close Event macro. It will run any time the file is opened or closed.

And then there's the "strawberry" option. Set the macro I gave you to run from a icon on your Quick Access Toolbar. Add a summary line to your file, hit the icon and boom! all nicely color formatted.

John