Assumptions: A1 contains the 3 digit 'code' to enter. Note: If this is
entered as text, then you don't need the A2 line, and just change all the A2
references from A3:A8 to A1. The first solution is if the numbers you are
comparing your 3 digits to are actual numbers and not text.
A2: =TEXT($A$1,"000")
A3: =--$A$2
A4: =--(LEFT($A$2,1)&RIGHT($A$2,1)&MID($A$2,2,1))
A5: =--(RIGHT($A$2,1)&LEFT($A$2,2))
A6: =--(RIGHT($A$2,1)&MID($A$2,2,1)&LEFT($A$2,1))
A7: =--(MID($A$2,2,1)&LEFT($A$2,1)&RIGHT($A$2,1))
A8: =--(MID($A$2,2,1)&RIGHT($A$2,1)&LEFT($A$2,1))
Then I named the range from A3:A8 to CheckList. CheckList is each of 6
possible combinations for a given 123 number.
Finally, my conditional formatting formula, assuming I am beginning to check
cells B1 to whatever, highligh selection, (and with B1 as active cell), go to
Conditional Formatting, change the first dropdown to formula is, and your
formula is:
=AND(B1<>"",NOT(ISNA(VLOOKUP(B1,CheckList,1,FALSE))))
If, however, your table of numbers is actually text, and not numbers, then
all you need to do is remove all the double dashes from A3:A8
Hope this helps!
