none
Conditionally Format Box Text per Row RRS feed

  • Question

  • I am tracking numerous projects (not linked) and display the project cost to the left of each Gantt bar.  I want to format the color of the text (dollar amount) from Cost, based on the value in Text6 (this field contains the funding status).  If the project is funded, the Cost should be green.  Unfunded it should be red, etc.  The macro works, but formats the text for all entries, not just the row. So after it runs all the rows are formatted based on the last row's result.  Is there a way to get it to format the text for just one row, and not all of them?

    Sub FormatFundingBarText()

    Dim tskT As Task
     
    For Each tskT In ActiveProject.Tasks
        Select Case tskT.Text6
            
            Case "Funded"
                SelectRow Row:=tskT.ID, RowRelative:=False
                TextStyles32Ex Item:=13, Color:=5287936
            
            Case "Unfunded"
                SelectRow Row:=tskT.ID, RowRelative:=False
                TextStyles32Ex Item:=13, Color:=255
            
            Case "Partially"
                SelectRow Row:=tskT.ID, RowRelative:=False
                TextStyles32Ex Item:=13, Italic:=True, Color:=0
            
            Case Else
                SelectRow Row:=tskT.ID, RowRelative:=False
                TextStyles32Ex Item:=13, Color:=0
               
        End Select

    Next tskT

    End Sub

    Monday, July 15, 2019 3:09 PM

All replies

  • KevinDonnelly,

    The reason you only see the last color setting is because when you select a task row you are only selecting the row on the left side of the Gantt Chart view, you are NOT selecting the Gantt Bar for that row. Then when you use the TextStyles32EX Method, you are indeed setting the Gantt Bar text but it is a universal setting, there is no separate command for setting the Gantt Bar text format for each Gantt Bar.

    So unfortunately the bottom line is, you can't do what you want. However, here's an idea for a workaround that you might want to try. Since you have only three funding conditions, use the GanttBarFormat Method to set the Cost field on the left, right, or inside based on "Funded", "Unfunded", or "Partially" case. You can then use the TextStyles32Ex Method to set the appropriate color for the location of the Gantt Bar Text.

    Another approach, you CAN individually color format the Gantt Bars, or the text in the Cost field.

    Hope this helps.

    John


    Tuesday, July 16, 2019 2:19 AM
  • Thanks, John.  I was afraid that it was a universal setting, and I appreciate confirmation of that.  Unfortunately, we're already using other text locations around the bar for other info (we're already abusing that capability pretty far and publishing more text with the Gantt than I'd prefer).  I'll look into color-coding the bars themselves, probably.
    Tuesday, July 16, 2019 3:40 PM
  • KevinDonnelly,

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

    John

    Tuesday, July 16, 2019 8:08 PM