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

Looks Awesome, man!
However, is giving me a syntax error, In this (red) passage. I'm not sure exactly what's wrong...

Answer 3

ThiagoBM2021,

Do you perhaps use a semicolon as your system delimiter instead of a comma? If you do then convert all commas to semicolons.

If that is not the problem then perhaps it is the extra blank lines that were generated when you pasted the code. Get rid of spaces between lines.

John

Answer 4

Hi John,

Yes, it was the extra blank line. It's working perfectly!!

One last question... is there a list of colour codes that I could use to customize and/or add more colours/levels to the code when required?

Thank you so much.

Answer 5

ThiagoBM2021,

You're welcome and thanks for the feedback. If I answered your question, please consider marking my response as the answer.

There might be a list somewhere but I don't know where. I use a simpler approach. I record a macro while manually setting desired colors for font and cell backrounds. Then I use the values recorded by that macro to incorporate into the macro I'm writing.

John