Solved

In MS Project change the color of the text for an entire row based on what is indicated in the "Status" field

Posted on 2016-08-24
10
66 Views
1 Endorsement
Last Modified: 2016-09-13
I am looking to change the color of the text for an entire row based on what is indicated in the "Status" field.  

I have searched other forums and have found solutions that are close, but nothing that really does what I am looking for.  I am aware of the highlight feature and of the conditional formatting, but I can't get them to do what I need. I wrote some VB code to do this exact thing about 5 years ago, but I am a novice, it took me about 5 days to write the very basic code, and then i lost it when I left my last job. :-(

So, I am looking to just have some VB that will do the following:
If Status = "Late" then color the text in the row to "RED"
If Status = "On Schedule" then color the text in the row to "GREEN"
If Status = "Future Task" then color the text in the row to "BLACK"
If Status = "Complete" then color the text in the row to "GRAY"

I would love to get advanced and have something like the following:
If Status = "On Schedule",  AND   % complete is < 85%,  AND   "Finish Date" is < 5 days in future,  then color the text in the row "ORANGE"

Any help is appreciated !!!
1
Comment
Question by:Keith Blanco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 41768760
Use a Macro like and substitute OutlineLevel with Status
Sorry don't have MS Project right now to try
Sub ColorFormatOL()
Dim t As Task
Dim i As Integer

i = 1
For Each t In ActiveProject.Tasks
       SelectRow row:=i, rowrelative:=False
       Select Case t.OutlineLevel
           Case 1
           Font32Ex CellColor:=&HB37F15
           Case 2
           Font32Ex CellColor:=&HD6982E
           Case 3
           Font32Ex CellColor:=&HF6BE41
           Case 4
           Font32Ex CellColor:=&HF7D577
       End Select

i = i + 1
Next t
End Sub

Open in new window


You can also see a detailed example here for conditional formatting in MS Project
https://social.msdn.microsoft.com/Forums/office/en-US/e2fd5a05-8803-4d66-a201-ab0f40cbdde6/custum-field-formula?forum=projectprofessional2010general
0
 

Author Comment

by:Keith Blanco
ID: 41769065
Thanks.  I'll give that a shot this afternoon.
0
 

Author Comment

by:Keith Blanco
ID: 41769083
Ok.  I just ran this.   thanks, for putting this together.   I have a couple of questions.

1.) the coloring that it does colors the row, or highlights the row.  is there a way to just change the color of the text?

2.) I cannot really see the difference in the color of the rows.  they seem to all be blue.  a few are a different shade of blue, but i do not see a differentiator between the rows (different colors, yet they have the same status, same due date, etc.)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Keith Blanco
ID: 41769256
this code looks like it changes based on "outline Level."   I need it to change based on the words in the column titled "Status."

Any idea what i use to identify that column?
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points (awarded by participants)
ID: 41769522
i hate to sit and watch for the question to be flagged neglected.

so, here you go, i wrote this code for you.  it does not only take care of the first part, but it goes beyond to the advanced section you mentioned in your original post.


Sub ChangeFontColor()


' Solution by ProfessorJimJam. EE Quqestion 28965268
 
 Dim tskT As Task
 
 For Each tskT In ActiveProject.Tasks
     Select Case tskT.Status
      Case pjOnSchedule
             SelectRow Row:=tskT.ID, RowRelative:=False
             Font Color:=pjGreen
             
  If CDate(tskT.Finish) < CDate((ActiveProject.CurrentDate) + 5) And CInt(tskT.PercentComplete) < 85 Then
          SelectRow Row:=tskT.ID, RowRelative:=False
          Font32Ex Color:=49407
      End If
      
      Case pjComplete
             SelectRow Row:=tskT.ID, RowRelative:=False
             Font32Ex Color:=12566463
      Case pjLate
             SelectRow Row:=tskT.ID, RowRelative:=False
             Font Color:=pjRed
      
      Case pjFutureTask
             SelectRow Row:=tskT.ID, RowRelative:=False
             Font Color:=pjBlack
             
             
     End Select
  
 Next tskT

 End Sub

Open in new window

1
 
LVL 18

Expert Comment

by:xtermie
ID: 41769836
great work Professor JJ...sorry for not responding Keith, but there is probably a time difference (it is 10am here now)...What JJ wrote should work out great.
1
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41769879
thanks xtermie.
0
 

Author Comment

by:Keith Blanco
ID: 41771082
THank you.   Works for me.   Thanks for your time and work here.  (you saved me about a week of my life :-)  

I want to be respectful of your time - i am willing to make a donation or pay for a gig or something...

Key notes for anyone using this are:

You must Expand all lines prior to running this, or it will not run properly (i.e. if any subtasks are grouped and hidden, then it will throw off all coloring for the remainder of the tasks.)   So if you had the project plan with subtasks expanded and contracted a certain way for ease of reading, you will need to perform that expansion and reconfiguration every time.
1
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41771270
@Keith-Blanco

You are most welcome. Please don't worry about donation gig. It didn't take more than few minutes of my evening when I was enjoying my tea and typing the code. What matters is that code works for you and that makes me happy that I was able to help.

Cheers.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41795675
Answer provided and accepted by OP
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Read about the ways of improving workplace communication.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question