Conditional formatting for cells that have data validation, List

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
432
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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
1,653
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,237
Messages
5,836,181
Members
430,406
Latest member
pmav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top