Highlight Cells Outside of Data Validation List

sarahmoa

New Member
Joined
Jun 5, 2016
Messages
16
Hi! I have a spreadsheet with a Data Validation column referencing a range on a separate sheet. What I'd like to happen is to highlight any cells not meeting that criteria. I know it throws the Data Validation error, but I'm having trouble getting people to understand I can't work with data outside of the list, but I don't want to lock them down from free-texting either. I'd like to highlight the cell to give them a visual queue that more information is needed.

I'm familiar with Conditional Formatting, but I haven't found a way to do this without creating a separate rule for each item in my list (20 items so far).
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Assuming the DV is in A2 downwards & the list is on a sheet called list in A2:A100 try
=ISNA(MATCH(A2,List!$A$2:$A$100,0))
 
Upvote 0
That works great! I modified it to fit my spreadsheet with: =ISNA(MATCH(D11,Data!$A$1:$A$20,0))

Is there a way to make it ignore blanks also, or am I reaching? :P
 
Upvote 0
How about
=AND(D11<>"",ISNA(MATCH(D11,Data!$A$1:$A$20,0)))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,224,915
Messages
6,181,724
Members
453,064
Latest member
robatthe2A

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
Back
Top