Conditional formatting for cells that have data validation, List

jerry12302

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
1,121
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,692
Members
425,230
Latest member
DzOus

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