Change CellColor Based On Resource on Task

[Dean]

A collegue asked me to if I could do "conditional formatting" in Project 2007
wherein a row is highlighted when a specific resource is chosen in the
Task.ResourceNames field. I'm attempting to follow this example:

http://blogs.msdn.com/project_programmability/archive/2007/02/12/vba-event-handler-example.aspx

I have the sample code working, but when I attempt to change it to highlight
the row based on the Resource Name, nothing happens. I receive no errors.

Here is the code I'm attempting:

Public WithEvents App As Application
Public WithEvents Proj As Project

Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal
Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)

If (Field = pjResourceName) Then
If (InStr(NewVal, "Member Firm") = 1) Then
ActiveCell.CellColor = pjYellow
End If
End If

End Sub

Can anyone help with this?

 

[Rod Gill]

Hi,

So what should happen when there are two or more resources on a task?

The field pjResourceName is a Resource field which can't be displayed in a
Gantt chart. Try:
If (Field = pjTaskResourceNames ) Then

If you make the instr test >0 instead of =1 then the cell will format if the
resource is assigned with another, not just if it's the first resource.
--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Dean]

Rod,

Brilliant! That worked great.

My intention was to highlight the whole row and I haven't been able to get
that to work. I tried inserting: SelectRow Row:=0 in there, but I got an
error. Is there a way to select the whole row?

Here's the code as it is currently working:

Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal
Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)

If (Field = pjTaskResourceNames) Then
If (InStr(NewVal, "Member Firm") > 0) Then
' SelectRow Row:=0
ActiveCell.CellColor = pjYellow
End If
If (InStr(NewVal, "Member Firm") = 0) Then
ActiveCell.CellColor = 16

End If

End If

End Sub

 

[Jan De Messemaeker]

Hi,

Just SelectRow will do.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf