Hi Lee

Can the teachers not enter 8 space 6 (8 6) for 8 years and 6 months?

If not, then you could copy the following event code to the sheet, which
will convert 8.6 to 8 6
The teachers can enter 8.0 through 8.11
They must enter 8.0 for 8 years

Private Sub Worksheet_Change(ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Target.Column = 5 Then
If Right(Target, 2) "11" Then
MsgBox "You cannot enter " & Target.Value
Target = ""
GoTo Endsub
End If
.AddReplacement ".", " "
Else
.DeleteReplacement "."
End If
End With
Endsub:
On Error GoTo 0
End Sub

Copy the Code above
Right click Sheet tab View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

This is event code which will be triggered automatically.
--
Regards
Roger Govier

"Lee West" wrote in message
...
That was incredible Roger...thanks you.

A couple of stumbling blocks though.

I have the three formulas set up exactly as you've suggested, just making
a
few alterations to take in my cell references.

I have childs DOB in B4, which then gives me the years/months in C4, the
lower limit in D4 and the upper limit in E4.

I want to be able to draw my conditional formatting in cell F4 and G4. In
these two cells the teachers will be entering the actual results from
testing
and for the cells to then change colour accordingly.

I have set up the formatting as you have said, in this case if it's lower
than D4 to to turn red, if it's between D4 and E4 to turn amber and if
above
E4 to turn green.

However, I can't get it to work as cells F & G4 are still treating numbers
as decimals....can i convert the formulas to give C, D & E4 as decimals?