Mutiple Rows to One Row based on Criteria

[beefreak]

Hi all. This is my first forum post. I have a file with students and test scores, attached below as "Test Data File" with two tabs (original/desired).

Briefly, I'd like to condense this data into one row per student ID, with each "Code" appearing as a column. Then, I'd like to format the text in the cell to red IF the "Status" associated with the code is "0".

I have exhausted Google searching for a solution to this, which I foolishly thought would be fairly straightforward. I think my main problem could be that I'm not sure how to approach this need. Is it a function? A formula? A VLOOKUP? VBA?

Clearly, my Excel knowledge is far too basic to handle this need and I'd appreciate any and all assistance you experts can give.

Thank you in advance for your expertise, time, and patience.