change colour based on specific text.

[Geek Girl 007]

I have asked this question before, however that was based on a single factor, I want to write a formula that has quite a big field.
I want any number that starts with a C (1 to 3) and is between 11 and 27 and any that starts with C4 to change colour.
Can this be done???

C-1-020

C-1-019

C-1-018

C-1-014

C-1-011

C-1-009

C-1-008

C-2-029

C-2-028

C-2-026

C-2-024

C-4-021

C-4-013

C-4-012

C-4-010

C-4-004

C-4-002

C-3-037

C-3-036

 

[denzo36]

Is there always a dash between the letter and numbers?
so examples of ones you'd want to colour?

C-1-021
C-2-012
C-3-456
C-4-654
C-11-871
C-27-111

 

[Geek Girl 007]

Hi there,
Yes, that's the way the numbers are generated, is that an issue???

 

[Cubist]

Try:

Book1
	A
1	C-1-020
2	C-1-019
3	C-1-018
4	C-1-014
5	C-1-011
6	C-1-009
7	C-1-008
8	C-2-029
9	C-2-028
10	C-2-026
11	C-2-024
12	C-4-021
13	C-4-013
14	C-4-012
15	C-4-010
16	C-4-004
17	C-4-002
18	C-3-037
19	C-3-036
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A19	Expression	=LET(t,MID(A1,3,1),ta,--MID(A1,5,3),IF(--t=4,1,(ta>=11)*(ta<=27)*SEARCH(t,"123")))	text	NO

 

[Geek Girl 007]

Hey, this is great, but I feel I forgot to mention that there are other letters in the pile.... so sorry.

G-1-009

H-4-004

D-2-023

G-3-003

C-1-007

H-4-015

C-2-028

D-1-002

H-4-003

H-4-026

A-3-043

A-3-043

K-4-016

A-4-034

G-4-013

C-2-013

C-3-018

D-3-017

A-2-035

D-3-025

B-2-036

 

[Cubist]

Book1
	A
1	C-1-020
2	C-1-019
3	C-1-018
4	C-1-014
5	C-1-011
6	C-1-009
7	C-1-008
8	C-2-029
9	C-2-028
10	C-2-026
11	C-2-024
12	C-4-021
13	C-4-013
14	C-4-012
15	G-4-010
16	C-4-004
17	C-4-002
18	C-3-037
19	C-3-036
20	G-1-009
21	G-1-009
22	H-4-004
23	D-2-023
24	G-3-003
25	C-1-007
26	H-4-015
27	C-2-028
28	D-1-002
29	H-4-003
30	H-4-026
31	A-3-043
32	A-3-043
33	K-4-016
34	A-4-034
35	G-4-013
36	C-2-013
37	C-3-018
38	D-3-017
39	A-2-035
40	D-3-025
41	B-2-036
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A41	Expression	=LET(t,MID(A1,3,1),ta,--MID(A1,5,3),IF(LEFT(A1,1)<>"C",0,IF(--t=4,1,(ta>=11)*(ta<=27)*SEARCH(t,"123"))))	text	NO