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

 

[CA_Punit]

@Fluff i got your point

@Newbienew
I have doubt
So if AZ value is more than Zero and in Sheet 1 i have value which is BAZ1 Should it be highlighted

 

[Newbienew]

@ CA_Punit
I am not sure what you mean by BAZ1. But if you are saying if AZ on sheet two has a value greater than 0 than on sheet one that rating should be highlighted regardless if its AZ1, AZ2, AZ3, or whatever comes after the Z.

@ Fluff
Just two questions based on what your formula and what I think might have played a factor. The other columns on sheet 1 have additional info in them. I had do deleted it as I thought it was not required. The question is can I put your code on sheet two and it still work? And the second question, sheet one is a short listing of ratings, they can get up to the 380th row in which the information is copied and pasted on sheet one frequently. Would this affect your formula based on the structure of it?

 

[CA_Punit]

I meant say you have value AZ in sheet 2 which has value greater than zero

And in sheet 2 you have say a word BAZ1. Should BAZ1 be highlighted as AZ is there is BAZ1.

 

[Newbienew]

I hope that I am answering this the way I understand your question. There is no BAZ1. This might be a typo as I went to look for it but did not see it.

However, IF BAZ1 was a rating it the formula would only count BAZ if it was a rate.

 

[Fluff]

The conditional formatting needs to go on Sheet1 in order to highlight the ratings. If you have other information on that sheet, the formula in col F can easily go in another column.

 

[CA_Punit]

I suppose your condition will require "&" Operator and might not support Conditional Formatting directly. So with Helper column i have tried to incorporate all the conditions

New Project 75.xlsb

Microsoft Excel Workbook

1drv.ms

 

[Newbienew]

I see what you mean. Thank you so much