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]

So what i understood from your explanation is that you want to highlight the column a and B if there is value in Column A and Non-Zero in Column B
if this is your requirement please consider this

Book1
	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
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J55	Expression	=AND($I2<>"",$J2>0)	text	NO
A2:B56	Expression	=AND($A2<>"",$B2>0)	text	NO

 

[Newbienew]

Sorry, I always seem to not be able to explain thru typing. I have two sheets. One the first sheet I have A Column and B Column along with I column and J Column. The A and I columns have the first letters of the rating. On a completely different sheet, I have those ratings but they have different characters after first letters. Take A3, it says ABF, one the other sheet column E could have ABFAN, ABF3, ABF2, ABF1, ABFC, ABFCS all in that seem column. I need a conditional format that will be able to match up the first letters to turn those rating green despite what comes after the first letters based on if column B and J is greater than 0. I hope this makes more sense than my initial posting

Your formula still worked out for me as I was using

Copy of REM SLOW WORKBOOK AUG5.xlsb
	A
2	A
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A56,I2:I55	Expression	=IF(B2>0,TRUE,"")	text	NO

To do the job, Thankyou

 

[CA_Punit]

I suppose you want this

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A148	Expression	=SUM(IFERROR(SEARCH(FILTER(IF(Sheet1!$B$2:$B$55>0,Sheet1!$A$2:$A$55),IF(Sheet1!$B$2:$B$55>0,Sheet1!$A$2:$A$55)<>FALSE)&"*",$A1),0))>0	text	NO

 

[CA_Punit]

FILTER(IF(Sheet1!$B$2:$B$55>0,Sheet1!$A$2:$A$55),IF(Sheet1!$B$2:$B$55>0,Sheet1!$A$2:$A$55)<>FALSE)&"*"

Sheet 1 is the Sheet as per Post 2

 

[CA_Punit]

Ok i didn't see you need J Column also so consider this formula

=(SUM(IFERROR(SEARCH(FILTER(IF(Sheet1!$B$2:$B$55>0,Sheet1!$A$2:$A$55),IF(Sheet1!$B$2:$B$55>0,Sheet1!$A$2:$A$55)<>FALSE)&"*",$A1),0))+ SUM(IFERROR(SEARCH(FILTER(IF(Sheet1!$J$2:$J$55>0,Sheet1!$I$2:$I$55),IF(Sheet1!$J$2:$J$55>0,Sheet1!$I$2:$I$55)<>FALSE)&"*",$A1),0)))>0

 

[Newbienew]

My version of excel does not have the filter formula function. I thought I was pasting it wrong but yes this what I am looking for. The conditional format to go in on the other sheet. It would just have to do that for all the ratings and change them blue just as in your example there

 

[CA_Punit]

But your profile shows office 365 version.???? Let me try Which version do you use???

 

[Fluff]

But your profile shows office 365 version

Not all 365 users have dynamic array functions, some people on the semi-annual channel have not yet had the latest update.

 

[Newbienew]

My apologies, I will update that. I did have 365 on home computer but my work one has 2016. Now I just have 2016.

@ Fluff, that has happen to me a few times when i did have 365