Small Project Schedule Using Conditional Formatting

[Bpickeri]

For a lot of the small projects my company does, we use a simple excel schedule rather than a full on P6 or Microsoft Projects schedule. The format used is very simple and outdated and I am attempting to spruce it up and make it more user friendly. I have attached what I have completed so far with a few sample items entered into the schedule so it is easy to see the formatting I have used so far. However, I have ran into two issues I cannot seem to solve.

1) My first issue (and likely the easier to solve) is progress tracking. I want to be able to enter an actual start date and actual duration and have a solid bar fill the referenced date cells rather then the spotted fill. However, I also want the bar to change from yellow when the status is "In Progress" and green when the status is (Completed). I feel like I need to enter in a formula using AND into the conditional formatting but I cannot seem to get it quite right.

2) My second issue is that these small projects often run stop shifts (no work is done on days off). I have set up the conditional formatting to show red on the required dates when a "Stop" rotation is selected. However, because work is not occurring on those days, the amount of working days showed in the schedule in green is less than the duration of the activity entered into column D. I need to find a way to detect the amount of days off during an activity and add that number to the end of the activity itself. The "Planned End Date" should then update to reflect the change. I have attempted to use User Defined Functions, Macro's, Countif() statements and a variety of different techniques but I am starting to wonder if it is even possible to make this an automatic process!

Any help with these issues would be greatly appreciated!

[Bpickeri]

I managed to solve my first issue which accounts for progress tracking but any help on issue 2 would be appreciated!