Flaging identical data in 3, 5 and 20 consecutive cells in a column

[SNarfel]

Hi,

I’m hoping someone can help me with the following.

The scenario is that for a class of students I want to monitor absence and a cell to go yellow on 3 consecutive absences, orange on 10 consecutive absences and red on 20 consecutive absences. Student name will run across the top row. The first column will be for dates. Underneath the names, absence will be monitored by inserting an “A” in the appropriate cell for the student and relevant date. Where a student has attended, the cell we be left empty. So you may end up with something as below

Date...........Jack......Rose.....Smith......Martha
1-1-07..........A..........A..............................
2-1-07..........A..........A..............................
3-1-07..........A...................A.....................
4-1-07...............................A.....................
5-1-07..........A......................................A..

Flag Row....YELLOW


Is what I am after possible? If so how please.

Thanks