VBA syntax to add formula in Data Validation Error alert

[sds1126]

Hi,

Part of one of my macros puts data validation in a range of my cells. The validation is dates between 1/1/2013 and date(year(now()),12,31. I am wondering how to express the formula in the error alert. Here's what I have so far for my code, with where I need help highlighted in red:


Range("A6:B10000").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1/1/2013", Formula2:="=date(year(now()),12,31)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Date"
.InputMessage = ""
.ErrorMessage = "You have entered an invalid date. Please enter a date between 1/1/2013 and date(year(now(),12,31)."
.ShowInput = True
.ShowError = True
End With


Thanks all for your help.

[sds1126]

Just an update, as I've been working to try and figure out a solution while waiting for replies:

Formula2 = Sheets("Formula Sheet").Range("A2").Value

In cell A2 on "Formula Sheet" i have the formula =date(year(now())+1,12,31)

Range("A6:B10000").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1/1/2013", Formula2:="=date(year(now())+1,12,31)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Date"
.InputMessage = ""
.ErrorMessage = "You have entered an invalid date. Please enter a date between 1/1/2013 and " & Formula2 & "."
.ShowInput = True
.ShowError = True
End With



I would like to build the formula into the VBA macro rather than have it refer to cell A2 on a worksheet, but I don't know the syntax of how to express that in the errormessage field.

Anyone have any insights?