Excel 2007 : Lookup formula that skips columns and can identify highlighted cells

Hello!

I'll try to explain this as best as I can. I am a school counselor and I need to track attendance at our school. I have an Excel 2007 spreadsheet set up that has two main tracking columns: A (Absences) and T (Tardies). These two are populated out for the duration of the school year and attached to dates for every Friday.

In a separate column, I want to put a formula that will tell me if the student's absences/tardies have hit a predetermined criteria level. Now it gets tricky: across the student's row, there is all of their weekly attendance data. A pair of cells in the row (i.e., one each of A/T from that particular date) is highlighted to indicate that they have hit the preliminary threshold. The separate tracking column formula should identify which of the cells in that row is highlighted, look through only the absence data in that row, and compare the number of absences in the latest report (i.e., the last A-column cell with data) with the number of absences in the highlighted cell. It should then run the same function for the number of tardies, this time looking only at the tardy data and comparing it only with the highlighted tardy number earlier in the row. Finally, the formula should determine which difference is the largest of the two (between tardies and absences), and then return that number.

Got it?

So in general, I need a formula that does all the following:
1. Distinguishes between even (i.e., Absences/A) and odd (i.e., Tardies/T) columns while looking up data
2. Identifies a highlighted cell in the row
3. Calculates the difference between the last data point in the row and the highlighted cell (still distinguishing between A and T columns)
4. Compares the above calculations for even and odd columns, and returns the larger of the two.

This is way outside of my skill level, and is currently taking up TONS of valuable time, so I would greatly appreciate any help! Thanks!!!

**edit** I have attached a sample file. The column labeled "Count" is where I want the formula to be inputted.
Attendance.xlsx