Debra

Many thanks for this, it works fine. One problem I have with it is that the
range "dob" extends further, ie, it contains about 200 rows of empty cells
which the user will complete.

The validation is there but when the user checks for validation errors
(Tools | Auditing | etc) it will show up errors in these cells until he/she
enters data. Is there any way to amend the formula to avoid seeing any
errors in the blank cells in range "dob" (column I)?

Gareth

"Debra Dalgleish" wrote in message
...
You can remove the check mark from Ignore Blanks, and revise your
formula slightly:

With Range("dob").Validation
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _


Formula1:="=AND(I2=29221,I2<=TODAY(),I2<=J2,OR(IS BLANK(K2),I2<=K2),OR(ISBLA
NK(L2),I2<=L2))"
.IgnoreBlank = False
.ErrorTitle = "Date of birth"
.ErrorMessage = "Entry must be a date between 01/01/1980 and today."
& Chr(10) & _
Chr(10) & "It cannot be greater than either the on, off or died
date."
End With


Gareth wrote:
I have the folowing code which works fine when there is data in all the
cells referenced. Unfortunately, column's K and L do not always have
data
in them (all cells are date cells).

This causes the validation not to work.

With Range("dob").Validation
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="=AND(I2=29221,I2<=TODAY(),I2<=J2,I2<=K 2,I2<=L2)”
.ErrorTitle = "Date of birth"
.ErrorMessage = "Entry must be a date between 01/01/1980 and today." &
Chr(10) & _
Chr(10) & "It cannot be greater than either the on, off or died date."
End With

Is there a way around it?

Many thanks.

Gareth




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html