that worked for me. In fact this worked as well:

Sub aatester1()
Range("A5:A13").Validation.Delete
Range("A5:A13").Validation.Add _
Type:=xlValidateCustom, _
Formula1:="=COUNTIF(INDIRECT($K$27),A5)=1"
End Sub

--
Regards,
Tom Ogilvy


"Brad E." wrote:

In the Worksheet_Change event, I want to change data validation depending on
certain entries in the spreadsheet. When the VBA gets to the following code:

Range("A5").Validation.Delete
Range("A5").Validation.Add Type:=xlValidateCustom,
Formula1:="=COUNTIF(INDIRECT($K$27),A5)=1"

where K27 = "A5:A13", and I don't want a duplicate in A5:A13.

I get the following error message: "Method 'Add' of object 'Validation'
failed". Does anyone know why my VBA won't add data validation?

Thanks in advance for any help. Brad E.