I have been tasked with trying to computerize a Daily Observation Report for a 911 center. Users will answer Evaluation questions with a score between 1-7 or with N.O. for "not observed" or N.R.T. for "not responding to training". What I would like is that, depending upon the value placed in the answer, a color will appear on another sheet in a single cell---green for values 4 or greater, red for values less than 4, yellow for NO, and black for NRT. Each set of questions would be answered daily, and the colors would appear on a date-separated form on another sheet which would allow for a quick, visual representation of a trainee's progress over the course of successive days or weeks. There would be approx 24 different questions for the trainer to answer. There would be nothing other than the color "response" on the other page---no additional data. The resulting page would just be multiple colored cells that show improvement or lack thereof as well as sections that have not been observed (N.O.).

It would also be great if, when hovering over the blank field, a tool tips type of box could appear that would contain the scoring standards for each respective question. These standards are question specific and differ from one to another (don't know if this is possible btw).

I know this is asking a lot, so any help or direction would be appreciated.