Validation on Pasted Values

[grey]

Hi,

I've got a spreadsheet with some list validation (ie user picks one of several possible values) which works great

However, this is going to be dealing with quite large amounts of data, so I've got a macro that will populate the data in the column (ie pastespecial values)

This has the problem that validation is only applied when users type data, and not when pasting data in
As in this problem:
http://www.excelforum.com/excel-gene...en-pasted.html

What I'd like to do is either find some way to apply validation to pasted data (so to trigger excel into running validation on the pasted data), or highlight data which doesn't match one of the listed validation entries .

I think I could probably attempt the second option here via a macro, but in order to do this I'd need to:
A) work out how to determine if a cell contains validation rules via VBA, or
B) work out how to do a logical test to see if a value is contained within the named range which contains my list items for the drop down list (I can get it to do this via a loop to compare to each individual value, but given the amount of data involved, I'm just wondering if there's a simpler/more efficient way to do this)

Can anyone help me with either of these please?

Thanks!

[romperstomper]

If you loop through the cells and check the Validation.Value, this will return True if the criteria are met (and by extension, if there are no criteria).