OK, So I am trying to create a tracker for curriculum proposals. 
I want the "Current Status" cell in the row to auto-populate with the column header text based on the one with the most recent date, and I also want the first 4 cells' color to change to correspond with the status.
So, when I enter the date for received, I want the Current Status to be "received" and the first four to be blue. Then, when I add the date for the Agenda, I want it to change to "On Agenda," and the color to be yellow, and so forth.
I could probably just add a dropdown and do conditional formatting based on text, but I would like it to auto-populate.