2

I have a spreadsheet that has a chart for several different objects that lists 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:............Object 2............Object 3:
Left Edge:........... 2 ........................0.......................3
Right Edge: .........6.........................2.......................8
Top: ....................1........................10......................8
Bottom: ...............6 .......................12 .....................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.

  • 1
    The bottom of Object 1 is 4 and the top of Object 3 is 5, is this what you intended? Right now I'm not visualizing them overlapping, but sharing different parts of the same columns (col 3-6). – Jason Aller Jan 20 '14 at 5:28
  • I apologize; I made a mistake. It should be fixed now. Thanks for pointing that out. – user291525 Jan 20 '14 at 5:32
  • Is an Excel macro an acceptable solution to your problem? Some folks may not be able to use them because of active directory applied group policies. – Jason Aller Jan 20 '14 at 5:37
  • I am rather new to Excel, so I have no experience with Macros. If there is an easy Macro solution, I'd love to use it, but I will need some extra help with the implementation. – user291525 Jan 20 '14 at 5:43
  • 1
    In writing the VBA macro I'm running into a few things that need clarification. Are the numbers in the same cell, or in the cell to the right of the label? Can this be run as a keystroke after selecting the range containing the Objects to check for overlap? Does it need to act like conditional formatting? – Jason Aller Jan 20 '14 at 5:51
0

A VBA macro can do what you are looking for. On Windows Alt+F11 will bring up the VBA editor.

The general pseudo code to follow would be:

  • with the selection grab the first row
  • clear the highlight format from it
  • use For Each to walk horizontally across it
  • with each Object store the values for the 4 cells below it in named variables
  • then in an inner loop compare these values to all of the Objects to the right
  • comparison would logical and together horizontal and vertical overlap tests
  • mark both cells if there is an overlap

Using this method if you have 10 boxes you check 45 pairs rather than 100 (10*10)

0

Something else to consider:

Sub DetectOverlaps()
  Const OBJECTCOUNT As Long = 3
  Dim i As Long, j As Long
  Dim ArrObj1 As Variant, ArrObj2 As Variant
  Dim Object1 As Range, Object2 As Range
  Debug.Print: Debug.Print
  For i = 1 To OBJECTCOUNT - 1
    j = i + 1
    While j <= OBJECTCOUNT
      ArrObj1 = Range("A1").Offset(1, i).Resize(4, 1)
      ArrObj2 = Range("A1").Offset(1, j).Resize(4, 1)
      Set Object1 = CoordinatesToRange(ArrObj1)
      Set Object2 = CoordinatesToRange(ArrObj2)

      Debug.Print "checking " & i & "-" & j, Object1.Address & " vs. " & Object2.Address,
      If Application.Intersect(Object1, Object2) Is Nothing Then
        Debug.Print "  -->ok"
      Else
        Debug.Print "  -->COLLISION"
      End If
      j = j + 1
    Wend
  Next i
End Sub

Function CoordinatesToRange(RangeAsArray As Variant) As Range
' RangeAsArray contains coordinates, in order: left, right, top, bottom
  Set CoordinatesToRange = Cells(RangeAsArray(3, 1) + 1, RangeAsArray(1, 1) + 1).Resize(RangeAsArray(4, 1) - RangeAsArray(3, 1) + 1, RangeAsArray(2, 1) - RangeAsArray(1, 1) + 1)
End Function

This makes use of the Application.Intersect method to detect overlapping ranges (which are built from rectangular coordinates). The ranges are not really meaningful here, but as long as the coordinates are between 0 and the max allowable row/column in your version of Excel, this will tell if there is a collision between any two range pairs.

I've assumed your definition of "Objects" is rooted at A1 and has one row and one column header. The constant at the top is just cheap code to specify the number of Objects of interest; you can vary to suit or perhaps make this dynamic in your application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy