I've tweaked by requirements a small bit and I've tried to twaek Max's
code above but not getting what I want

Instead of allowing Multiple "CC" AND up to 6 unique digit numeric
values, within the range J10:J22. I want:-

Any one of "CC1" or "CC2" or "CC3" or "CC4" (but only once used) AND
up to 6 unique digit numeric values, within the range J10:J22.

Below is my attempt but it allows me to type "CC5" and indeed any
alpha value, which I don't want to allow


=AND(OR
(J10=1,J10<999999,J10="CC1",J10="CC2",J10="CC3",J 10="CC4"),COUNTIF
($J10:J22,J10)=1)