On Wed, 2 Sep 2009 13:01:04 -0700, ker_01 wrote:

Salgud-

Try changing:
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreater , Formula1:="=MONTH(H" & lCurRow & ")=MONTH(I" & lCurRow
&")"

to
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MONTH(H8)=MONTH(I8)"

I believe the issue is not with the formula, but the use of the date/greater
than parameters.

Please note that this still isn't perfect; the data validation appears to
allow a date in one cell and a blank in the other to still evaluate as 'True'
and allow data entry in K7. You may have to add a few conditions to your
formula to address this issue.

HTH,
Keith

Thanks for your reply. As I said in my original post, the actual custom
validation formula inserted by the macro is

=MONTH(H7)=MONTH(I7)

which is the same thing (different row) as what you recommended. If I just
put the "7" in the code, all subsequent rows would validate based on the
dates in row 7, which is not what I want. I need to have the row be dynamic
for the spreadsheet to work, but so far, I have no indication that it's the
dynamic part that's causing the problem. The resulting formula is exactly
what I wanted it to be, it's just not validating valid dates. Or am I
missing something here?

"salgud" wrote:

Trying to validate in the following scenario:
Cells H7 and I7 have dates in them (testing using 8/3/09 and 8/4/09).
Before an entry can be made in K7, I have a custom formula to check and see
that both dates in H7 and I7 are in the same month. The following code is
supposed to enter the validation criteria:

Public Sub DataValidationBDOCsmEDOC()

With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreater, Formula1:="=MONTH(H" & lCurRow & ")=MONTH(I" & lCurRow &
")"
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "EDOC not same month as BDOC"
.InputMessage = ""
.ErrorMessage = _
"The BDOC and the EDOC are in different months." _
& Chr(10) & "Please correct these dates before entering the rates."
.ShowInput = False
.ShowError = True
End With

End Sub

The resulting custom formula in the Data Validation box is

=MONTH(H7)=MONTH(I7)

But when I put in dates of 8/3/09 and 8/4/09, it tells me they aren't in
the same month. When I put "=MONTH(H7)" and "=(MONTH(I7)" in other cells,
they both evaluate to 8. So why am I getting an invalid data message?

Thanks in advance.