`
`Create your own conditional formatting in MS Project
`If a task is past its deadline, would you like that information to automatically appear in bold
`red letters in Microsoft Project? Here's how to use Visual Basic to create a simple macro to
`perform that, and other, nifty tricks.
`
`By Brian Kennemer | November 7, 2000, 12:00 AM PST
`
`Project 2000 has a host of features that you can use to format your project plans and control
`their attributes. But one feature that is missing in Project 2000 is the ability to conditionally
`format text in task lines. This powerful feature, which is included in Excel, assigns different colors
`to the task line to provide visual clues that indicate the current status of each task.
`
`Project 2000 users don’t need to work without this feature, however. This article covers the
`basics on how you can use Visual Basic to easily create a macro that will place conditional
`formatting on your task list.
`
`The macro will convert the task text from black and white display (above) to color (below).
`
`CiM Ex. 1043 Page 1
`
`
`
`A macro to color the task text
`Here is a macro that will color the task text based on the Task Cost field value. It uses the
`Select…Case statement to evaluate the Cost field. Based on certain thresholds, it will tell Project
`what color to make the task.
`
`Sub SelectTaskRows()
`Dim tskT As Task
`
`For Each tskT In ActiveProject.Tasks
` Select Case tskT.Cost
` Case 1 To 500
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjGreen
` Case 501 To 2000
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjBlue
` Case 2001 To 5000
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjFuchsia
` Case Is > 5000
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjRed
` End Select
`Next tskT
`
`End Sub
`
`The Select…Case statement is perfect for this kind of macro because it allows the user to specify
`a field and then to specify different criteria. The result is that certain code will run depending
`upon which of the criteria the field meets.
`
`The line that reads “Select Case tskT.Cost” is setting up which field the rest of the statement will
`be evaluating. Next, there are a series of four Case statements. Each of these statements lays
`out criteria against which the value of the Cost field should be compared. If the Cost field meets
`
`CiM Ex. 1043 Page 2
`
`
`
`the criteria, then the code on the lines just below that Case will be run. In this way, we can give
`our Cost thresholds and specify the colors we want for each cost level.
`Would you like to receive more advice and ideas about MS Project? Sign up for TechRepublic’s
`MS Project TechMail now (http://www.techrepublic.com/techmail_sample.jhtml?
`listdesc=Project&thislist=e021&brdcrm=My+TechMails&successPage=%2Ftechmails.jhtml) to receive Brian
`Kennemer’s daily tipsheet.
`This basic concept is very flexible. We could customize the macro to evaluate any field and
`format the text according to that field’s values. We could change the first line of the Select…Case
`to evaluate Percent Complete rather than Cost. Then we would set up the other Cases below
`that and specify the formatting we wanted.
`
`The Font method can communicate deadlines
`The Font method used in the above macro takes several arguments that control the font
`properties of the specified text. While the Font method can accept several arguments that allow
`you to control many aspects of the font, the above macro uses only the color argument. You can
`also specify bold, italics, font name, and font size. These other arguments may be useful when
`you design your own formatting macros. There are as many possibilities for what criteria to use
`for the formatting as there are project managers in the world.
`
`For example, inserting this For…Next loop into the above code in place of the existing loop will
`format the task text based on the SV (Schedule Variance). This will provide a status report and
`tell you how far behind or ahead a task is compared with its own baseline.
`For Each tskT In ActiveProject.Tasks
` Select Case tskT.SV
` Case Is < 0
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjRed, Bold:=True
` Case Is = 0
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjBlue
` Case Is > 0
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjGreen, Bold:=True
` End Select
`Next tskT
`
`CiM Ex. 1043 Page 3
`
`
`
`
`
`Any task that has a negative SV is considered behind and will be colored red. Any task that has a
`positive SV is considered ahead of schedule and is colored green. Zero SV Tasks are right on
`schedule and colored blue.
`Post your question to the TechRepublic Technical Q&A area
`(http://www.techrepublic.com/forumqa/main.jhtml).
`Connect your newsreader to news://msnews.microsoft.com/ (news://msnews.microsoft.com/) and
`then search for the group called microsoft.public.project.vba.
`
`Location by color
`You may also format the tasks based on the contents of a text field. Perhaps you have a text field
`that lets you know the location assigned to a task. Is the task taking place in the Washington
`office or in New York? You will know the answer instantly by looking at the color rather than
`checking the value of the text field.
`
`The For…Next loop to create this would look like the following:
`
`For Each tskT In ActiveProject.Tasks
` Select Case tskT.Text1
` Case "New York"
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjTeal
` Case "California"
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjBlue
`
`CiM Ex. 1043 Page 4
`
`
`
` Case "Washington"
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjGreen
` Case "Florida"
` SelectRow Row:=tskT.ID, RowRelative:=False
` Font Color:=pjRed
` End Select
`Next tskT
`
`
`Adapting these samples for your own uses is easy. On the line that reads “Select Case tskT…” just
`insert the field you want to test after the period where SV, Text1, and Cost are in the above
`samples. Then, you need to adjust the condition that appears just after each of the “Case”
`statements that are below the Select…Case line. Lastly, you need to edit the Font line so that the
`formatting you want will be applied for each of the given cases.
`Do you have questions about Microsoft Project? Post a comment
`(http://www.techrepublic.com/forumdiscuss/post.jhtml?parent_id=0) to this article or send an e-mail to Brian
`(mailto:ITManager@techrepublic.com?subject=Brian's%20questions). He can't answer every inquiry, but he
`will write articles about the topics and questions that readers ask about most frequently.
`
`Recommended
`
`
`
`by Taboola Promoted Links
`
`
`
`
`Earn 10% Cash Back In Rotating Categories With This Amazing Credit Card
`CreditCards.com
`
`7 Reasons Why Glasses Should be Bought Online
`GlassesUSA.com
`
`Kate Middleton Drops Jaws, Proving Prince William Is A Lucky Man
`Livingly
`
`Review: 'Down the Deep Dark Web' is a movie every technologist should watch
`
`Next-gen light bulb could turn any surface into a computer screen, as envisioned by CMU professor
`
`CiM Ex. 1043 Page 5
`
`
`
`Free Windows 10 upgrade still available to these select users
`
`WHITE PAPERS, WEBCASTS, AND DOWNLOADS
`
`White Papers // From CA Technologies
`
`Cross-Enterprise Workload Automation helps reduce cost and complexity, improve service
`delivery
`
`CA Workload Automation solutions have been critical enablers of mission critical
`online business strategies and have helped improve business performance in a
`number of ways, such as improving mission-critical investment "readiness to trade"
`completion rates from 82% to 99.5% at a major financial services firm.
`
`DOWNLOAD NOW
`
`White Papers // From Brocade
`
`Improve ROI and business processes with ADCaaS
`
`READ MORE
`
`White Papers // From CA Technologies
`
`The Total Economic Impact of CA Workload Automation iDash
`
`DOWNLOAD NOW
`
`White Papers // From Brocade
`
`Keep apps running with global load balancing
`
`White Papers // From Microsoft
`
`Six Steps to Control the Uncontrollable
`
`READ MORE
`
`READ MORE
`
`CiM Ex. 1043 Page 6
`
`
`
`FEATURED CONTENT
`
`Future of IT Careers
`The on-demand economy
`is changing work-for-hire
`models around the world.
`
`In a noisy office?
`Apply for a free trial of the
`Jabra EVOLVE noise-
`cancelling office headset
`
`EDITOR'S PICKS
`
`IBM Watson: The inside story
`
`Rise of the million-dollar smartphone
`
`CiM Ex. 1043 Page 7
`
`
`
`The world's smartest cities
`
`The undercover war on your internet secrets
`
`RECOMMENDED
`
`Sponsored Links
`
`Earn 10% Cash Back In Rotating Categories With This Amazing Credit Card
`CreditCards.com
`
`7 Reasons Why Glasses Should be Bought Online
`GlassesUSA.com
`
`DON'T MISS OUR LATEST UPDATES
`
`News Update
`
`Week in Review
`
`SUBSCRIBE
`
`
`
`SUBSCRIBE
`
`by Taboola
`
`
`
`
`CiM Ex. 1043 Page 8
`
`
`
`
`
`
`
`
`
`Related Ads
`
`1 Free Web Hosting
`
`2 Brand New IPhone
`
`3 Certification Programs
`
`4 Microsoft Excel 2010
`
`5 Malware Removal
`
`6 Cloud Computing Companies
`
`7 Free Microsoft Office
`
`8 Agile Project Management
`
`CiM Ex. 1043 Page 9