Thanks for your reply.

On Thu, 27 Aug 2009 17:34:48 -0500, Chip Pearson wrote:

First of all, your formula is syntactically wrong. Paste it into a
cell directly, and Excel will bitch that the formula is no good. I'm
guessing that what you really want is

=AND((I7H7),MONTH(I7)=MONTH(H7),YEAR(I7)=YEAR(H7) ,I7DATE(YEAR(G7),MONTH(G7),DAY(G7)+21))

I think you guessed wrong. (Hard to guess right if the formula is wrong).
G7 is the client DOB. The situation is that H7 and I7 contain the dates
that service started and finished. The criteria a
1. I7H7 (service ended after it started)
2. Both must be in the same month and year (invoiced monthly)
3. I7 must be less than 21 yrs after G7 (service is for people under 21
only)

I grabbed this one in a hurry when I was posting, but it may not have been
one of the ones I had this particular problem with. I'll test before I post
next time.

Now, what input are you validating? That is, what cell is the
validation applied to and what are some examples of valid and invalid
inputs?
See above. There are a number of others where I've had the same problem to
the point where I'd pretty much given up on these kinds of complex
validations being entered via VBA. None of the ones I tried, even with help
here in the newsgroup, worked.


Also, remember that validation is applied only when a cell's value is
changed by the user. If the cell is changed via VBA code, validation
rules are ignored.
I had figured that much out. On cells where the data is calculated, I don't
have any validation. Only on cells entered by the user.

Note, too, that it is possible to set up a set of custom validation
formulas in several cells that preclude any of those cells from being
changed. Essentially a mutually exclusive circular error.

Not possible here. I did the validations from left to right, as the users
will be entering the data that way. So I'm always validating a cell to the
left of the current cell.

Here's another example of one I could never get to work. It goes in cell
D7, and checks to see that the client ID (the first being in cell B7)
consists of 7 characters, one letter followed by six numbers (A123456).

=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(U PPER(B7),1))64,CODE(LEFT(UPPER(B7),1))<91)

This formula works fine when put directly into the cell, but when I put it
in code, I get an Object defined error.

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=
_
xlGreater,
Formula1:="=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1), CODE(LEFT(UPPER(B7),1))64,CODE(LEFT(UPPER(B7),1)) <91)"
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "bad"
.InputMessage = ""
.ErrorMessage = _
"bad" _
& Chr(10) & "Please correct these dates before entering the rates."
.ShowInput = False
.ShowError = True
End With

Someone suggested I remove the second = sign (in front of the AND). That
gets rid of the object defined error, but then when I test it, it tells me
that a valid Client Id isn't valid and allows the entry.

I have others, but I'll stop here and see if you, or someone, can tell me
what's wrong with this one. Maybe it's the same thing with the others.

Do you see what I'm missing?