Data validation using code
 

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

Data validation using code
 

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(ISBLANK(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