Using Conditional Formatting to Highlight Positions

[Newbienew]

Hello

I am attempting to do a complicated "for me" conditional format. I want to be able to highlight any ratings on a separate spreadsheets column (E) based on the column A and I of the rating and column B and J if its greater than 0. I am not about to move these columns. The issue is column E on the separate sheet has different variables at the end of the ratings, ranging from 1,2,3,sn,an,cn,fn and c, cs. The formula would have to be able to turn any rating green on the E column of the other sheet regardless of the variable endings. I have posted the A and B columns first ant the E column of the other sheet as well. Please help Thank you.

Copy of REM SLOW WORKBOOK AUG5.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Rate	Available	BNA		ASchool				Rate	Available
2	A	0	0		0				ABE	0
3	ABF	5	0		0				ABH	0
4	AC	0	0		0				AD	0
5	ADEK	0	0		0				ADSP	0
6	AE	0	0		0				AECF	0
7	AG	0	0		0				AIRC	0
8	AIRR	0	0		0				AM	0
9	AME	0	0		0				AMEK	0
10	AN	0	0		0				AO	0
11	AS	0	0		0				AT	0
12	AV	0	0		0				AW	0
13	AWF	0	0		0				AWO	0
14	AWR	0	0		0				AWS	0
15	AZ	30	0		0				BM	0
16	BU	0	0		0				CE	0
17	CM	0	0		0				CS	0
18	CSS	0	0		0				CTI	0
19	CTM	0	0		0				CTN	0
20	CTR	456	0		0				CTT	0
21	DC	0	0		0				E	0
22	EA	0	0		0				EM	0
23	EMN	0	0		0				EN	0
24	EO	0	0		0				EOD	0
25	ET	0	0		0				ETN	0
26	ETR	0	0		0				ETV	0
27	FC	0	0		0				FCA	0
28	FN	0	0		0				FT	0
29	GM	45	0		0				GSE	0
30	GSM	0	0		0				HM	48
31	HMBHT	0	0		0				HMDA	0
32	HMDH	0	0		0				HMFM	0
33	HML	0	0		0				HT	0
34	IC	0	0		0				IS	0
35	IT	0	0		0				ITS	0
36	LN	0	0		0				LS	0
37	LSS	0	0		0				MA	0
38	MC	0	0		0				MM	0
39	MMA	0	0		0				MMN	0
40	MMS	0	0		0				MMW	0
41	MN	54	0		0				MR	0
42	MT	0	0		0				MU	0
43	NC	0	0		0				ND	0
44	NUC	0	0		0				OS	0
45	PC	0	0		0				PR	0
46	PS	0	0		0				QM	0
47	RP	0	0		0				RS	0
48	S	0	0		0				SB	0
49	SECF	0	0		0				SENG	0
50	SH	0	0		0				SK	0
51	SKS	0	0		0				SN	0
52	SNL	0	0		0				SO	0
53	STG	0	0		0				STS	0
54	SW	0	0		0				UCT	0
55	UT	0	0		0				YN	0
56	YNS	0	0		0
Sheet2

Copy of REM SLOW WORKBOOK AUG5.xlsb
	E
2	ABE2
3	ABE3
4	ABEAN
5	ABECS
6	ABF1
7	ABF3
8	ABFAN
9	ABFAN
10	ABFC
11	ABFFAN
12	ABH1
13	ABH2
14	ABH3
15	ABHAN
16	ABHC
17	AC2
18	ACCS
19	AD2
20	AD3
21	ADAN
22	AE1
23	AE2
24	AE3
25	AG3
26	AM1
27	AM2
28	AM3
29	AMCS
30	AME1
31	AME2
32	AME3
33	AN
34	AO
35	AO2
36	AO3
37	AOAN
38	AOAN
39	AOCS
40	AS1
41	AS2
42	AS3
43	ASCS
44	AT
45	AT1
46	AT1
47	AT2
48	AT2
49	AT3
50	AWS1
51	AZ1
52	AZ2
53	AZAN
54	BM1
55	BM2
56	BM3
57	BMC
58	BU2
59	CE3
60	CM2
61	CM3
62	CMDCS
63	CS1
64	CS2
65	CS3
66	CSS2
67	CSS3
68	CSSN
69	CTN1
70	CTN2
71	CTN3
72	CTR1
73	CTR2
74	CTT1
75	CTT2
76	CTT3
77	DC2
78	DC3
79	EM2
80	EM3
81	EMC
82	EMN1
83	EMN2
84	EMN3
85	EMNCM
86	EN2
87	EN3
88	EO3
89	ET1
90	ET2
91	ET3
92	ETN1
93	ETN2
94	ETN3
95	ETV2
96	FC1
97	FC2
98	FC3
99	FCA1
100	FCA2
101	FCA3
102	FCAC
103	GM1
104	GM2
105	GM3
106	GSCS
107	GSE1
108	GSE2
109	GSEC
110	GSM2
111	GSM3
112	GSMFN
113	HM1
114	HM2
115	HM3
116	HMCS
117	HN
118	HNSN
119	HT1
120	HT2
121	IC1
122	IC2
123	IC3
124	IS1
125	IS2
126	IT1
127	IT2
128	IT3
129	ITSN
130	LN2
131	LS1
132	LS2
133	LS3
134	LS3
135	LSC
136	LSSC
137	LSSN
138	MA1
139	MA2
140	MA3
141	MAC
142	MASN
143	MC1
144	MC2
145	MC3
146	MM1
147	MM2
148	MM3
149	MM3
REM TEAM JUN

Copy of REM SLOW WORKBOOK AUG5.xlsb
	E
150	MMA2
151	MMFN
152	MMN1
153	MMN2
154	MMN2
155	MMNC
156	MN1
157	MT1
158	MT2
159	MU3
160	NC1
161	NCC
162	OS2
163	OS3
164	OSSN
165	PR1
166	PR2
167	PS
168	PS2
169	PS3
170	QM1
171	QM2
172	RS3
173	SB1
174	SB2
175	SH2
176	SHSN
177	SO1
178	STG1
179	STG2
180	STG3
181	STS1
182	STS2
183	STS3
184	STSCS
185	TBD
186	TM2
187	TM3
188	YN2
189	YN3
190	YNSC
REM TEAM JUN

 

[Newbienew]

@CA_Punit Any luck on the 2016 version. I Am still struggling myself.

 

[CA_Punit]

I suppose I posted it somewhere. Let me check

 

[CA_Punit]

I tried but it was not working. So i posted a new threat as to why it was not working. Unfortunately it didnt work
have a look.. I am trying still

Conditional Formatting not working

So i am trying to drag rate from column A for which values from column B are greater than zero and trying to match with Rate (Data) from column A with Column Q . Since Q has more alphabet than column A so i have used "*" to find the partial match Say for example ABF from Column A is available...

www.mrexcel.com

 

[CA_Punit]

Try This

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Rate	Available
2	A	0									FALSE			ABE2
3	ABF	5									FALSE			ABE3
4	AC	0									FALSE			ABEAN
5	ADEK	0									FALSE			ABECS
6	AE	0									TRUE			ABF1
7	AG	0									TRUE			ABF3
8	AIRR	0									TRUE			ABFAN
9	AME	0									TRUE			ABFAN
10	AN	0									TRUE			ABFC
11	AS	0									TRUE			ABFFAN
12	AV	0									FALSE			ABH1
13	AWF	0									FALSE			ABH2
14	AWR	0									FALSE			ABH3
15	AZ	30									FALSE			ABHAN
16	BU	0									FALSE			ABHC
17	CM	0									FALSE			AC2
18	CSS	0									FALSE			ACCS
19	CTM	0									FALSE			AD2
20	CTR	456									FALSE			AD3
21	DC	0									FALSE			ADAN
22	EA	0									FALSE			AE1
23	EMN	0
24	EO	0
25	ET	0
26	ETR	0
27	FC	0
28	FN	0
29	GM	45
30	GSM	0
31	HMBHT	0
32	HMDH	0
33	HML	0
34	IC	0
35	IT	0
36	LN	0
37	LSS	0
38	MC	0
39	MMA	0
40	MMS	0
41	MN	54
42	MT	0
43	NC	0
44	NUC	0
45	PC	0
46	PS	0
47	RP	0
48	S	0
49	SECF	0
50	SH	0
51	SKS	0
52	SNL	0
53	STG	0
54	SW	0
55	UT	0
Sheet1

Cell Formulas
Range	Formula
K2:K22	K2	=SUM(IFERROR(SEARCH(IF($B$2:$B$55>0,$A$2:$A$55),N2),0))>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N2:N22	Expression	=SUM(IFERROR(SEARCH(IF($B$2:$B$55>0,$A$2:$A$55),N2),0))>0	text	NO

 

[Fluff]

If you're happy with a helper column, maybe this monstrosity.

+Fluff New.xlsm
	E	F
1
2	ABE2	ABE
3	ABE3	ABE
4	ABEAN	ABE
5	ABECS	ABE
6	ABF1	ABF
7	ABF3	ABF
8	ABFAN	ABF
9	ABFAN	ABF
10	ABFC	ABF
11	ABFFAN	ABFF
12	ABH1	ABH
13	ABH2	ABH
14	ABH3	ABH
15	ABHAN	ABH
16	ABHC	ABH
17	AC2	AC
18	ACCS	AC
19	AD2	AD
20	AD3	AD
21	ADAN	AD
22	AE1	AE
23	AE2	AE
24	AE3	AE
25	AG3	AG
26	AM1	AM
27	AM2	AM
28	AM3	AM
29	AMCS	AM
30	AME1	AME
31	AME2	AME
32	AME3	AME
33	AN	AN
34	AO	AO
35	AO2	AO
36	AO3	AO
37	AOAN	AO
38	AOAN	AO
39	AOCS	AO
40	AS1	AS
41	AS2	AS
42	AS3	AS
43	ASCS	AS
44	AT	AT
45	AT1	AT
46	AT1	AT1
47	AT2	AT
48	AT2	AT2
49	AT3	AT
50	AWS1	AWS
51	AZ1	AZ
52	AZ2	AZ
53	AZAN	AZ
54	BM1	BM
Sheet1

Cell Formulas
Range	Formula
F2:F54	F2	=IF(OR(E2={"sn","an","cn","fn","cs"}),E2,IF(OR(RIGHT(E2)={"1","2","3","c"}),LEFT(E2,LEN(E2)-1),IF(OR(RIGHT(E2,2)={"sn","an","cn","fn","cs"}),LEFT(E2,LEN(E2)-2),E2)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E190	Expression	=OR(IFERROR(INDEX(Sheet2!$B$2:$B$56,MATCH(F2,Sheet2!$A$2:$A$56,0)),0)>0,IFERROR(INDEX(Sheet2!$J$2:$J$55,MATCH(F2,Sheet2!$I$2:$I$55,0)),0)>0)	text	NO

 

[Fluff]

@CA_Punit
With your sample, try changing B2 to 1

 

[CA_Punit]

Hi @Fluff

But that was his requirement . if any rows in column B has a number greater than 0, we have to check whether the corresponding word is available in Column N as prefix

 

[Fluff]

Yes but if you change B2 to 1, every code that contains the letter A will be highlighted, which I don't think is what the OP wanted.

@Newbienew
If your happy with my helper column approach in post#15, here is a simpler formula for the CF
=COUNTIFS(Sheet2!$A$2:$I$57,F2,Sheet2!$B$2:$J$57,">0")

 

[Newbienew]

I attempted to use both but not sure if I did something with truing to use it or not. Then al also noticed an issue. I have a CSSN which has CS and SN. I found that out as I couldn't figure out why it was blank. lol

 

[CA_Punit]

every code

Yes @Fluff I didn't Notice that. Any way
You can try this =SUM(IFERROR(IF(SEARCH(IF($B$2:$B$55>0,$A$2:$A$55),N7)>1,FALSE,SEARCH(IF($B$2:$B$55>0,$A$2:$A$55),N7)),0))>1