Excel personnel tracking sheet

Hello,

I am trying to make an excel sheet that counts consecutive days that a person is on the list...the list being days that certain personnel were present or not. For example, in the first column there is a list of names of personnel in alphabetical order. The names are highlighted either red or yellow depending if they were there that day....If the names are yellow 15 days in a row I want the name cell to turn a specific color and 16 days a different specific color. If the name column is red I want to reset the count back to zero. I figured that I could create a date column next to the name that changes to a set date once it is first highlighted yellow and creates the condition (first.yellow.date. + 16>actual date) and if red set conditions back to zero.......PLEASE ADVISE