Selecting a cell in VBA

[Adam4321]

Hi,

I'm trying to write a simple macro to go through a task list and change the
font of any cells that meet a certain criteria.

The code works in that it goes through all the tasks and checks if it fits
the criteria. But when I tell it to change the font it only does it on the
activecell. I don't know how to tell it to change the font on the cell being
tested or how to change the activecell in line with the condition checker,
I'm sure it's pretty simple but I'm struggling... help appreciated:

Sub Font_Change()

Dim AllTasks As Tasks
Dim Tsk As Task

Set AllTasks = ActiveProject.Tasks
For Each Tsk In AllTasks
If Not Tsk Is Nothing Then
If Not Tsk.Summary Then
If Tsk.PercentWorkComplete = 100 Then
'need to move the active cell to the current Tsk to change the font on the
correct cell!!
Font Color:=9
End If
End If
End If
Next Tsk

End Sub

 

[Piet Remen]

Hello Adam. These type of posts are better suited for the project.developer
forum but here is my response.

Probably several ways of attacking this but here is definately one scenario
for you.

Sub Font_Change()

Dim AllTasks As Tasks
Dim Tsk As Task

Set AllTasks = ActiveProject.Tasks
For Each Tsk In AllTasks
If Not Tsk Is Nothing Then
If Not Tsk.Summary Then
If Tsk.PercentWorkComplete = 100 Then
'make the name field of the task that matches our
criteria the active cell
'Tsk.ID gives us the the task row, "Name" is the task
column to select and false stops relative selection
Application.SelectTaskCell Tsk.ID, "Name", False
With ActiveCell
.FontColor = 9
End With
End If
End If
End If
Next Tsk

End Sub


Hope this helps.

Piet Remen
Solutioin Specialist
Strategic Data Management

 

[Adam4321]

Piet,

That's great, many thanks. I was messing around with the selecttaskcell
command but couldnt get it right,

Thanks again
Adam

 

[Jan De Messemaeker]

Hello All,

Notwithstanding Piet's very valuable approach, don't forget these commands
before looping through the tasks:

Filter apply "All Tasks"
Outlineshowalltasks

If not there is a risk ID does not give the exact row

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