You don't really need this check in the StudentAssign function:

If Not Intersect(Target, Range("B9:B43")) Is Nothing Then
since it was already done in the Change event. I would recommend a loop through the cells that are in that intersection in case more than one cell is changed at a time.