Update macro in Excel for critical path


Can you please help me to update macro that shows a critical path (x) for the sheet1. I have it working on a sheet2 but I moved it to sheet1 and it is not working looks like.

Also, is it possible to show a critical path on a graph with a different color? So when I change values in "Batch time" column the graph will change and color of critical path will be shown?

Thanks in advance!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Iryna,

Is this homework, or what do you need it for exactly?
Iryna253Author Commented:
no, this is not for the homework. this is for my work. I already asked help on it before.
Ejgil HedegaardCommented:
I don't see the function OnCriticalPath used anywhere, so I have added column K.

As I understand it, the input to the function are
TaskID = the value in column B, same row
TaskIDs = the range in column B from row 3 to 27
PacingTasks = the range in column D from row 3 to 27
OnCritPaths = the range in column J from row 3 to 27

The statements
vCritPaths = OnCritPaths.Value
vPacingTasks = PacingTasks.Value
assigns a value, but OnCritPaths and PacingTasks are ranges.
Replace with
vCritPaths = OnCritPaths
vPacingTasks = PacingTasks

Added column L and M for the chart.
Column L is the values in G when it is on the critical path, x in column K, red on the chart.
Column M is the values in G when it is not on the critical path, not x in column K, green on the chart.
Changed the blue to be automatic.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Iryna253Author Commented:
Thank you very much!
How about this one, Iryna?:
Was it homework?
Iryna253Author Commented:
Yes, it was. I resolved it. Sorry for asking, I will not ask this anymore HW questions. Thanks for your help.
Hi again Lryna,

At EE you are allowed to ask for help with homework assignments, but here are the official rules in detail:

A couple of main points are:
a) Make sure you clearly state in your first post that it is an assignment.
b) Don't ask experts to give you the answers.  Ask for guidance so that you can work out the answers yourself.
But you should read & follow all the rules in the links above.

Now, tell me Lryna:
Q1. Are you still getting your formal education?
Q2. What job do you do that requires solutions like this critical path Excel question?

I await your reply with answers to Q! & Q2.

Iryna253Author Commented:
Hello tel2, did yo receive my reply to this massage? I submitted it yesterday but I cannot find it.
All I can see is what is above, Lryna.
Please post your answers to Q1 & Q2 again and make sure they are visible on the page after submission.
Iryna253Author Commented:
Q1: Yes, i am still in school completing my degree this fall while working full time.
Q2: I am doing projects at work to calculate how many people we need if we will increase production. For this purposes I studied Project Management in school using Microsoft Project program, but I would like to use Excel instead. I will source your company in my report for helping me with automation. Will this be ok?
> I will source your company in my report for helping me with automation. Will this be ok?
I wouldn't if I were you, because:

a) I didn't help you with the automation.  It looks as if Ejgil did.
b) I don't have a company.
Iryna253Author Commented:
I will indicate Experts Exchange company in my report.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.