validate data using code
 

I have the following code which works fine on range dob:

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

Range dob does however contain blank cells and these show up as validation
errors. Is there any way to amend the code so as not to use .IgnoreBlank =
False, which I think is causing them to show as errors?

validate data using code
 

You can change the formula:


Formula1:="=OR(ISBLANK(I2),AND(I2=29221,I2<=TODAY (),I2<=J2,OR(ISBLANK(K2),I2<=K2),OR(ISBLANK(L2),I2 <=L2)))"


Gareth wrote:
I have the following code which works fine on range dob:

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

Range dob does however contain blank cells and these show up as validation
errors. Is there any way to amend the code so as not to use .IgnoreBlank =
False, which I think is causing them to show as errors?




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