Conditional formatting for cells that have data validation, List

[jerry12302]

I paste data from another source (document 1) into a spreadsheet (document 2), at the next available data row. All the cells in column D in document 2 have been assigned Data Validation, List, and reference a named range for the list (Payees). The new data pasted from document 1 does not have any validation in column D, it is just labels. Sometimes the data in column D from document 1 happens to match an item in the Payees list in document 2, which is fine, but many times it is not. All the new data in column D should also be assigned data validation, List, referencing Payees.

We can highlight the cells after pasting and add the data validation, but sometimes after pasting the list will get sorted different ways before anyone added the data validation. Is there a way to use Conditional Formatting to highlight cells in column D that do not have data validation assigned to them? I know I can assume if the value appears in the Payees list then it probably has data validation, but if at another time that cell needs to change to a new payee, you can't select from the list because there is no validation for that cell, it just happens to have data that matches a payee in the Payees list.

Can you test for data validation somehow when assigning conditional formatting?

 

[6StringJazzer]

There is nothing built in to do this. It could be done with a VBA user-defined function, if you want to go that direction.