quicker more efficient formatting macro needed

[Elvis]

I have a decent size schedule that gets statused each week with percent work
complete status, actuals starts, actual finishes and new ECD for start and
finish. Toss in the fact most items are networked and may need to be
modified for 'work arounds' this meeting is very time consuming and a large
portion is spent watching me make the entries.

to cut down on time I have created my own 'que sheet' for holding the
updates submitted from the team. Instead of having them read off the updates
they've made to a copy of theschedule, they just send it to me. I copy the
project fields they will be editing and then pasted them into a View created
with Dur1, start1, fin1, start2, fin2, start3, fin3 and a number field to
hold the percent complete. I then run a macro to compare the deltas from
their data to what I have in the standard project fields and highlight those
changes. we just walk through and review the changes and afterwards when
they are all agreed to, I on my one go through and make the changes.

trouble is: I have to access each cell currently in my code to change the
format and that takes serious time. In excel I can do that WITHOUT accessing
a particular field but do not know how to assign the format in Project. any
help speeding this up is appreciated.

I'll post the code in another string

Thanks so much for any ideas.

 

[Elvis]

here is what I have now except there are multiple ifs for eacf field evaluated:

Sub Status_Evaluation()

Dim Proj As Project
Dim T As Task

For Each T In ActiveProject.Tasks

variance = ""

If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then
SelectTaskField Row:=0, Column:="start1"
FontEx Color:=7, CellColor:=5
variance = "S/"
End If

Application.SelectCellDown

Next T

End Sub

 

[Rod Gill]

Try the code below, but you have questions to answer:

Sub Status_Evaluation()
Dim T As Task
For Each T In ActiveProject.Tasks
If not T is nothing then 'Handle empty rows
If T.Start1 <> T.Start Then
T.Marked=True
T.Text1 = "S/"
Else
T.Marked=false
T.Text1= ""
End If
End if
Next T
End Sub

Select Format, text Styles and format the Marked style as you want it. This
will automatically format all Tasks with Marked flag set to true.

What is Variance? It isn't a field, so I've stored the string in Text1
--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

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

here is what I have now except there are multiple ifs for eacf field
evaluated:

Sub Status_Evaluation()

Dim Proj As Project
Dim T As Task

For Each T In ActiveProject.Tasks

variance = ""

If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then
SelectTaskField Row:=0, Column:="start1"
FontEx Color:=7, CellColor:=5
variance = "S/"
End If

Application.SelectCellDown

Next T

End Sub







__________ Information from ESET Smart Security, version of virus
signature database 4937 (20100311) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4937 (20100311) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[John]

here is what I have now except there are multiple ifs for eacf field
evaluated:

Sub Status_Evaluation()

Dim Proj As Project
Dim T As Task

For Each T In ActiveProject.Tasks

variance = ""

If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then
SelectTaskField Row:=0, Column:="start1"
FontEx Color:=7, CellColor:=5
variance = "S/"
End If

Application.SelectCellDown

Next T

End Sub

Elvis,
Rod provided a very simple solution that highlights the full task line
for the changed rows. Hopefully that gives you something you can use.
However if you really want to only highlight those field cells that are
changed then the required code is a little more complex.

Unfortunately Project does not have the VBA text formatting capabilities
found in Excel. Generally text formatting in Project must be done using
foreground processing and that takes longer. Your simple macro code is
one way to do it but is very slow. There are ways to speed up the
process considerably. Here is a pseudo-code structure you might want to
pursue.
1. As you enter the change data in the spare fields, also set a flag
field
2. Filter on the flag field so the view displays only task rows with
changes
3. Select the first row and color format those fields with changes
4. Use Fill Down to color all change task rows
5. Apply the "All Tasks" filter

Hope this helps.

John
Project MVP

 

[Elvis]

Thanks Rod and John

I really need only the changed fields. My "variance" was an idea i had to
identify changed fields (like your suggestion to flag changed tasks).
knowing that the there is no direct way to format I will try your suggestion
John. Thanks

 

[Elvis]

Thanks John.

I only listed the one flag (Variance = "S/" to flag changes in the planned
start), but for each status field I was also collecting and adding this data
to a variable string for each task (i.e. "F/" = finish deltas, "AS/" = actual
start deltas, etc). So I was collecting the data already to have the
ability to filter for the different changes. Don't know why I didn't think
to put the formatting off until the end and format each field as a large
range.

with about 6K lines and many updates you helped me get from about 25-30
minutes to just over 4minutes with this simple change.