apply data validation on pasted values

[Neha72]

Hello All,
I Have a data validation VBA code but want the cells to be validated even if the cells to be validated are pasted values. .
So the user can enter the value and even paste it
With Range("l22:L1000").Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertWarning, Operator:= _
xlNotBetween, Formula1:="Range(j12),AND(Range(j11))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "See Threshold for Materiality"
.ShowInput = False
.ShowError = True
End With

 

[Chihiro]

Copy and pasting, will over write data validation applied to the cell as well.

There are two method to deal with this.
1. Use code to validate the value instead of using data validation.
2. Disable Copy/Paste operation on range that has data validation.

From your description, you'd want to allow copy paste. Then your only option is #1. However, your formula syntax in validation formula looks off.

Formula1:="Range(j12),AND(Range(j11))"

I'd suggest uploading sample workbook with your set up.