Filling form fields via worksheet dropdowns, user update via form, change form color

I have been trying to do this using various sources around the web, and I am frustrated to no end.

I have to make a form that pulls information from a worksheet and populates the fields based on the worksheet allowing the person to switch records using either of two drop down boxes, or gives the person the ability to type a number in one if the number they want is not there making a new record. I need it to update all fields at the time they select the drop down including the other drop down box, or whenever they re-open the form.

Then they need to be able to change the data in only 3 of the fields, Part, Place and Person, not completion status, and they can't change the notification number for the order and vice versa.

If they pull up a record that does not have the person populated, it changes the background color of a frame to red, if it is completed, change the background color of a frame to match the rest of the form.


I know this is a tall order, if anyone can help me out even on one part I would really appreciate it.

On a separate note I need to find out how to make the form sort the dropdown boxes in numerical order when selecting.

Attaching worksheet.