Is there a shorter way without having to repeat a Conditional Formatting formula?

[arthurz11]

 

[6StringJazzer]

Formula

=SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0

Applies to:

I2:J4

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	-10	7							-10,7-2	0,11-4
3	-5	2							-5,2-1	0,5-0		-5	2
4	5	11							5,11-3	-5,0-5		-5	0
5												5	2
6												5	-2
7												0	5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J4	Expression	=SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0	text	NO

 

[arthurz11]

Formula

=SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0

Applies to:

I2:J4

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	-10	7							-10,7-2	0,11-4
3	-5	2							-5,2-1	0,5-0		-5	2
4	5	11							5,11-3	-5,0-5		-5	0
5												5	2
6												5	-2
7												0	5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J4	Expression	=SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0	text	NO

I really appreciate your help in your time and effort. =SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0 The formula works beautifully!!!

 

[bebo021999]

Using COUNTIF may help code neater

=OR(COUNTIF(I2,$L$3:$L$7&","&$M$3:$M$7&"-*")>0)

Book1
	I	J	K	L	M
2	-10,7-2	0,11-4
3	-5,2-1	0,5-0		-5	2
4	5,11-3	-5,0-5		-5	0
5				5	2
6				5	-2
7				0	5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J4	Expression	=OR(COUNTIF(I2,$L$3:$L$7&","&$M$3:$M$7&"-*")>0)	text	NO

 

[arthurz11]

I really appreciate your help in your time and effort. =SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0 The formula works beautifully!!!

By the way, I didn't test it with the expanded columns. I have 150 columns. So if I use your formula: =SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0. I have to repeat for each pair of columns if I add additional columns. That means I still have to repeat the formula over and over again until I have written 75 formulas. For Example:
=SUM(IFERROR(MATCH($L$3:$L$7&","&$M$3:$M$7&"-*",I2,0),0))>0
=SUM(IFERROR(MATCH($N$3:$N$7&","&$O$3:$O$7&"-*",I2,0),0))>0
=SUM(IFERROR(MATCH($P$3:$P$7&","&$Q$3:$Q$7&"-*",I2,0),0))>0
=SUM(IFERROR(MATCH($R$3:$R$7&","&$S$3:$S$7&"-*",I2,0),0))>0

and so on...I mean, it will work fine and it will highlight the x,y coordinates that meets the condition. But it's the only way, correct?

 

[6StringJazzer]

By the way, I didn't test it with the expanded columns. I have 150 columns.

If you don't explain your entire problem in your first post, you won't get an entire solution. I'm guessing there is some way to do this but it's not obvious. I'll have another look.

 

[arthurz11]

If you don't explain your entire problem in your first post, you won't get an entire solution. I'm guessing there is some way to do this but it's not obvious. I'll have another loo

Ok. Thank you.

 

[6StringJazzer]

What are the factors that led to that layout? Why do you need 150 columns instead of 2?

 

[arthurz11]

What are the factors that led to that layout? Why do you need 150 columns instead of 2?

I tried figuring it out with one formula and it was difficult. So I decided to create 75 conditional formulas UNTIL your question gave me an idea. I'm going to use the =Cell & number to just create only two columns. That would be much easier and I just have one Conditional Formula. Thank you!