I recently came across this interesting question from Thomas in LinkedIn MS Project Schedule group
The simplified version of his requirement: Filter all milestones in the next 6 months along with its immediate predecessors. In the below plan, the current date falls in October and his requirement is to highlight Go Live 1, Build 1 and Build 2 (as Go Live 1 is the only milestone in the next 6 months and its immediate predecessor are Build 1 and Build 2)
I evaluated different options such as (a) custom fields with formula, (b) custom filters, (c) write VBA code. The first two options was eliminated as the formula can test only the current task and cannot test its predecessor tasks. To test the task and its predecessor, VBA macros is the solution.
Let’s start with a pseudocode
1. For each task in plan do
1.1. If task is not empty and active
1.1.1. If the task is milestone and is happening in the next 6 months
220.127.116.11. Mark the milestone to True
18.104.22.168. For each predecessor of the current task
22.214.171.124.1. Mark the current predecessor to True
2. Highlight all the tasks with Marked as True
Now you need to translate each statement by using Project model object, properties and methods.
1. Click View tab. Click Macros dropdown and select Visual Basic
2. In the editor, double click your current project on the left panel. This will display the code editor.
3. Now, insert a procedure named FindMilestonesAndImmediatePredecessors by selecting Insert menu, and then Procedure.
7. After marking the milestones and its immediate predecessors, you can highlight these tasks by creating a filter MilestonesAndImmediatePredecessors
8. Now to run this macro click View tab. Click Macros dropdown and select View Macros. Select the macro FindMilestonesAndImmediatePredecessors and click Run button