Test for Overlapping Regions

[Chelonian4]

I have a spreadsheet that has for several different objects the location of their left edge, right edge, top, and bottom relative to a point in the top left corner. I want to make a condition for formatting the cell containing the object as red whenever one of the areas it creates overlaps any of the other areas.For instance, if:

Object 1:
Left Edge: 2
Right Edge: 6
Top: 1
Bottom: 4

Object 2
Left Edge: 0
Right Edge: 2
Top: 10
Bottom: 12

Object 3:
Left Edge: 3
Right Edge: 8
Top: 5
Bottom: 8

Then Object 1 and Object 3 would light up red, since they overlap, but Object 2 would remain unlit, since it doesn't overlap anything else anywhere. I have made a program that successfully does this by OR-ing a long list of AND statements (three AND statements for every edge of every object) for each Object, but this is very messy, time-consuming, prone to errors, and is completely impractical for more than 4 objects. Is there a better way? If anyone could help me, I would greatly appreciate it.

 

[shg]

You'd need to generate all combinations on n choose two, and test each pair for overlap. But moving shapes around wouldn't trigger any event that would prompt them to be tested.

 

[Chelonian4]

I apologize; I made a mistake. The Bottom of Object 1 should be 6. If there is an "Edit Post" button, I haven't found it, so unfortunately, I couldn't correct the original post.

The shapes aren't actually drawn. The left edge, right edge, top, and bottom are literally listed in the spreadsheet:

	Object 1	Object 2	Object 3
Left Edge:	2	0	3
Right Edge:	6	2	8
Top:	1	10	5
Bottom:	6	12	8

<tbody>
</tbody>

Sorry if that wasn't clear originally.

 

[shg]

That makes it easier. If the cell values are changed manually, you could process it in the Change event. You need a pair of nested loops to check all combinations.

You need VBA to change the color of a shape.

 

[Chelonian4]

That makes it easier. If the cell values are changed manually, you could process it in the Change event. You need a pair of nested loops to check all combinations.

You need VBA to change the color of a shape.

The only thing I need to change the color of is the cell that says "Object #". What do you mean by "process it in the Change event"? As far as I know, loops can only be done in macros. Is there a non-macro way of efficiently doing this, or do I need to resort to them (my reluctance comes form my lack of experience with them)?

 

[shg]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		Obj01​	Obj02​	Obj03​	Obj04​	Obj05​	Obj06​	Obj07​	Obj08​	Obj09​	Obj10​
2​	Left	8​	5​	4​	0​	2​	1​	6​	6​	9​	7​
3​	Right	10​	7​	6​	2​	4​	3​	8​	8​	11​	9​
4​	Top	2​	6​	5​	9​	5​	9​	5​	2​	4​	8​
5​	Bottom	4​	8​	7​	11​	7​	11​	7​	4​	6​	10​
6​
7​	Intersects	0​	2​	1​	1​	0​	1​	1​	0​	0​	0​

The formula in B7 and copied right returns the number of other shapes intersected by this shape:

=COUNTIFS($B$2:$K$2, "<" & B3, $B$3:$K$3, ">" & B2, $B$4:$K$4, "<" & B5, $B$5:$K$5, ">" & B4) - 1

You could use it in conditional formatting to highlight cells in the first row (or wherever).

It assumes the orientation of screen coordinates: +x right, +y down.

 

[Chelonian4]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		Obj01​	Obj02​	Obj03​	Obj04​	Obj05​	Obj06​	Obj07​	Obj08​	Obj09​	Obj10​
2​	Left	1​	3​	4​	1​	7​	3​	6​	0​	6​	6​
3​	Right	3​	5​	6​	3​	9​	5​	8​	2​	8​	8​
4​	Top	8​	4​	8​	2​	8​	7​	8​	6​	7​	9​
5​	Bottom	10​	6​	10​	4​	10​	9​	10​	8​	9​	11​
6​
7​		0​	0​	1​	0​	3​	1​	3​	0​	2​	2​

<tbody>
</tbody>

The formula in B7 and copied right is returns the number of other shapes intersected by this shape:

=COUNTIFS($B$2:$K$2, "<" & B3, $B$3:$K$3, ">" & B2, $B$4:$K$4, "<" & B5, $B$5:$K$5, ">" & B4) - 1

You could use it in conditional formatting to highlight cells in the first row (or wherever).

Thanks a ton! I am quite new to Excel, so I wasn't aware of all of these functions that could be used. I'm quite impressed by its simple, modular format; I thought I'd have to resort to using macros. Thanks again for the help.

 

[shg]

You're welcome.