Removing Data Validation (list value) causes macro to not run?

Thanks for the reply TMS.

What I basically wanted to remove the DV for was to avoid allowing duplicate selections being entered into the cell. So if the drop-down has red, yellow, green and blue, add whichever item is picked (only one unique selection is allowed at a time). But if the cell already contains, say "red, yellow, blue, red" and if you pick red, then the drop-down should cancel.

I tried using INSTR to check for this with the oldVal and newVal variables, and while the check works if an item that already exists in the cell is picked from the drop-down list, I don't know what should be done to cancel the drop-down and break out of the program. If I try to use Exit Sub once the check is verified (INSTR(oldVal, newval) > 0), it just creates a situation where only one item is always added to the cell. The desired result is to have all the strings, but without any duplicates.

Any ideas on how this can be done?

Thanks again

Appreciate your looking into it!

I've attached the workbook.

1. "Main" sheet has the column of interest - "J". "K" is just a column that tracks the number of list entries in the row, which I included for reference.
2. DV is intact
3. "Backend" sheet has the list of colors.
4. If you click on a cell under column J, inside the table, you will get a userform which will allow you to pick colors from the list. You can cancel and use the drop-down to pick the colors.
5. Duplicate entries are detected, but if the cell is blank, it'll detect them in that case as well.

^ Thanks TMS for posting that code!

Works great, and I get to keep the DV