Less than, Greater Than, equal to conditional formatting

[Lee West]

I have a spreadsheet that i need to use to calculate children's reading ages
against their real ages with a 6 month swing either way, using a 3 colour
system.

For example. if a child is 8 years and 6 months old (Cell Ref B4 and listed
as a 2 decimal point number), and their reading age is in the range 8 years
to 9 year 2 months, I need that to show amber. If it's one month either side
of that range, i need it to show red if it's under and green if it's over.

I've got it working with specific number using less than, equal to or
greater than formuals, but it will only show amber for the exact DOB ie if a
child is 8.6 in B4, it will only show amber if the number in C4 is identical.

Does this make sense?

I also need to figure out how to create a custom format to take into account
there are 12 months in a year and not just 10 when using normal numbers.

Any help would be supremely appreiciated

 

[Roger Govier]

Hi Lee

Take a look at the undocumented Datedif function on Chip Pearson's site
http://www.cpearson.com/Excel/datedif.aspx

If you have the child's DOB in A1, enter the following formulae in B1, C1
and D1
=DATEDIF($A1,TODAY(),"y")&" "&DATEDIF($A1,TODAY()*1,"ym")

=DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY())),"y")&" "
&DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY())),"ym")


=DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"y")&" "
&DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"ym")

B1 will hold the child's actual age
C1 will hold the upper limit (age + 6 months)
D1 will hold the lower limit (age - 6 months)

Now, select B1>Format>Conditional Formatting>
Cell is>Between>C1 and D1 Format Amber
Add
Cell is>Less than>D1 Format Green
Add
Cell is>Greater than>C1 Format Red

 

[Lee West]

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?

 

[Roger Govier]

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.

 

[Lee West]

Excellent...thank you Roger