Comparing relative cells and making values dependent
 

Hi All,

It's amazing how many 'little things' are involved in making an
application. In a school workbook, there will be 3 columns,
"Attendance", "Performance", and "Behavior" that will be inserted with
a macro for each day of school. These 3 columns as well as others,
such as "Progress Notes" and "Assignments" (which can also be added via
macros) go horizontally, while each row is a student record.

If a teacher enters a 0 in a cell in the "Attendance" column, I'd like
Excel to automatically insert zeros for "Performance" and "Behavior",
which will always be the 2 columns to the right of "Attendance". This
makes sense because if a student is absent, he or she cannot perform
and receive behavior points. However, I do not want 0s or anything
filled in if the "Attendance" cells are left blank (no 0s showing).

Additionally, if a number from 1 to 5 is entered in an "Attendance"
cell, Excel should make sure that teachers enter values in the next two
columns, "Performance" and "Behavior". Scores for all three columns
must range from 0 to 5.

Lastly, I'd like for teachers to be able to put a letter into the
"Attendance" column, such as "x" or "X" for an excused absence.

Note that each of these columns will be used in formulas, so the
insertion of a letter should not interfere with calculations. Also
note that all cell addresses or references will be relative, but column
headings will remain the same across the sheet.

I came across a thread here that is partially useful--"how to compare
the active cell value to the previous cell's value" by Nigel, which
included the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If ActiveCell.Value < ActiveCell.Offset(-1, 0).Value Then
MsgBox "Do something"
End If
End If
End Sub

Any help expanding on this to include the desired functionality would
be greatly appreciated.

Sincerely,
Eric