Home Forums Discussion Help with VBA Formula for Status Colors

This topic contains 1 reply, has 2 voices, and was last updated by Sai Prasad Sai Prasad 3 days, 7 hours ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #411648 Reply

    Megan

    I am not well-versed in VBA, so I’m hoping someone can assist me :).

    We are on a complex Program and need a more complex Color Status field. I have the following defined separately but will need to be in a single equation when I’m done. I tried to combine them into a single line of code, I don’t get errors in VB Editor, but when I enter as a Formula for a field, I get a syntax error.

    'Future Task
        'On Track (Start/Finish = Baseline)
            White = IIf([Baseline Start] > [Status Date] And ([% Complete] = 0), 0)
        'Forecast Late Finish (Finish after Baseline Finish)
            Red Flag = IIf([Finish] > [Baseline Finish] And ([% Complete] = 0) And ([Start] > [Baseline Finish] Or [Start] = [Baseline Finish]), 6)
        'Forecast Late Start (Start after Baseline Start)
            Yellow Flag = IIf([Start] > [Baseline Start] And ([% Complete] = 0) And ([Finish] > [Baseline Finish] Or [Finish] = [Baseline Finish]), 7)
        'Forecast Finish early, Start early or on-time
            Green Flag = IIf([Finish] < [Baseline Finish] And ([% Complete] = 0) And ([Start] > [Baseline Finish] Or [Start] = [Baseline Finish]), 8)
    
          
    'Current/Past Tasks
        'Complete
            Grey = IIf([% Complete] = 100, 5)
            
        'On Schedule (Start early or on-time, Finish on-time) and % Complete on time
            Green = IIf(([Baseline Start] < [Status Date] Or [Baseline Start] = [Status Date]) And ([Start] < [Baseline Start] Or [Start] = [Baseline Start]) And ([% Complete] < 100) And (([Finish] = [Baseline Finish]) Or ([Finish] < [Baseline Finish])), 1)
          
        'Start Late, Finish Early or On Time; or % Complete not on track (should be 50% complete, but only 40%)
            Yellow = IIf(([Status] = "Late") And (([Finish] = [Baseline Finish]) Or ([Finish] < [Baseline Finish])) Or (IIf(([% Complete] > 100 * (Abs(([Status Date] - [Start]) / ([Finish] - [Start])))), 3)))
        
        'Finish Late (any start); or should have started but % Complete = 0
            Red = IIf(([Finish] > [Baseline Finish]) And ([% Complete] < 100), 4)
    #411660 Reply
    Sai Prasad
    Sai Prasad
    Participant
    • Community Leader
    • Forum Pro

    On the Yellow variable, you have subtracted [Start] from [Status Date] and [Finish] with – operator. This will not work in Formula. You need to use the function ProjDateDiff. I have combined all the formulas into one

    IIf([Baseline Start]>[Status Date] And ([% Complete]=0),0,IIf([Finish]>[Baseline Finish] And ([% Complete]=0) And ([Start]>[Baseline Finish] Or [Start]=[Baseline Finish]),6,IIf([Start]>[Baseline Start] And ([% Complete]=0) And ([Finish]>[Baseline Finish] Or [Finish]=[Baseline Finish]),7,IIf([Finish]<[Baseline Finish] And ([% Complete]=0) And ([Start]>[Baseline Finish] Or [Start]=[Baseline Finish]),8,IIf([% Complete]=100,5,IIf(([Baseline Start]<[Status Date] Or [Baseline Start]=[Status Date]) And ([Start]<[Baseline Start] Or [Start]=[Baseline Start]) And ([% Complete]<100) And (([Finish]=[Baseline Finish]) Or ([Finish]<[Baseline Finish])),1,IIf(([Finish]>[Baseline Finish]) And ([% Complete]<100),4,IIf(([Baseline Start]<[Status Date] Or [Baseline Start]=[Status Date]) And ([Start]<[Baseline Start] Or [Start]=[Baseline Start]) And ([% Complete]<100) And (([Finish]=[Baseline Finish]) Or ([Finish]<[Baseline Finish])),1,IIf(([Status]=”Late”) And (([Finish]=[Baseline Finish]) Or ([Finish]<[Baseline Finish])) Or ([% Complete]>100*(Abs(ProjDateDiff([Status Date],[Start])/ProjDateDiff([Finish],[Start])))),3)))))))))

Viewing 2 posts - 1 through 2 (of 2 total)
Reply To: Help with VBA Formula for Status Colors
Your information:




75 + = 83