Colour Coding

[chokhani]

I am a user of excel 2007, I have a worksheet (Summary) in which Column A Consist of Name of students and Column B has data validation with respect to course the students are enrolled in. As there are 8-10 courses with different modules and all modules are given a code from Column C to Column HH. What I want is when I type a student name in A Column and select his particular course in B Column from drop down data validation list,the codes with respect to his course should only be coloured (fill), this will help me in knowing what all modules/code has to be taught to him. Further there is a small complication as I have 3 faculties and they teach different modules to students so I have created a sheets (named on faculty) and have column heading as colA(students name) ColB(Course) ColC(Code)ColD(date)ColE(Remark),here remark refers to whether the particular student was absent or present during a particular session, if blank he was present and if absent I type A in that column. I need help as to when I type a particular code in faculty sheet with respect to particular student which he has completed this should be posted in the Summary sheet automatically so that I can have clear picture of the student progress and topics still to be covered for him. Also there are times when the same module is taught for more than a day. Hope I am clear please let me know if anything more is req. I can mail the sheet.