none
Customising MS Project Text Styles RRS feed

  • Question

  • Hi guys,

    I am trying to to link the colour of the text in the table by resource.

    I have seen a a macro that colours the background and have tried to adapt this for my means but cannot seem to get it to function properly.

    There are 5 resources that I am using:

    Close, NLT, Imp, PKO, SDTC.

    This is the macro that I found on here:

    Sub colormaint()
    OutlineShowAllTasks
    FilterApply "All Tasks"
    SelectAll
    Lines = ActiveSelection.Tasks.Count
    SelectRow 1
    For Ctr = 2 To Lines - 1
    SelectRow 1, True
    If InStr(ActiveSelection.Tasks(1).ResourceNames, "Maint") Then
    FontEx CellColor:=pjYellow
    Else
    FontEx CellColor:=pjWhite
    End If
    Next Ctr
    End Sub

    So effectively I need this to replicate 5 times.

    Thanks in advance

    Gary

    Monday, February 3, 2014 7:54 PM

All replies

  • Hi Gary,

    A simpler way (and possibly faster if you have a large number of resources) is to do it is to build a filter with the five resource names. Then apply the filter, select all, and apply the formatting.

    My code sample below should do this for you - just put the resource names you want to highlight in the constant at the top, seperated by commas (no spaces, but the filter will be case insensitive):

    Sub FormatSelectedResources()
    
    Const ResourceNames = "Close,NLT,Imp,PKO,SDTC"
    
    Dim pj As Project
    Dim res As Resource
    Dim i As Integer
    Dim str_SplitResourceNames() As String
    
    'Split the constant's value using the comma as the delimiter
    str_SplitResourceNames = Split(ResourceNames, ",")
    
    'build the filter to identify the resources
    Application.ViewApply "Resource Sheet", True
    Application.FilterEdit "Highlighted Resources", False, True, True, FieldName:="Name", test:="equals", Value:=str_SplitResourceNames(0), Operation:="or"
    For i = 1 To UBound(str_SplitResourceNames())
        Application.FilterEdit "Highlighted Resources", False, NewFieldName:="Name", test:="equals", Value:=str_SplitResourceNames(i), Operation:="or"
    Next i
    
    'clear any residual formatting that may be left over
    Application.FilterApply "All Resources"
    Application.SelectAll
    Font32Ex CellColor:=vbWhite
    
    'apply the yellow formatting to the selected resources
    Application.FilterApply "Highlighted Resources"
    Application.SelectAll
    Font32Ex CellColor:=vbYellow
    
    'apply the "All resources" filter
    Application.FilterApply "All Resources"
    Application.SelectBeginning
    
    End Sub

    I hope that helps,

    Andrew


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    Monday, February 3, 2014 10:50 PM