Forum Discussion

clh14_96's avatar
clh14_96
Copper Contributor
Sep 08, 2025

Clash analysis for manufacturing hall

Hi all,

I'm trying to build an excel sheet that has 3 "manufacturing bays" and when I list a move date & location against a piece of plate, that it moves a cell and recreates that live picture in that build hall. Initially I was trying to use some of the functionality in the (board game) Battleship excel templates to see if writing a VBA would allow for this/ linking it to a button. Something like the below:

 

I then want it to highlight if two plates are likely to be moved into the same space at the same time as part of the clash analysis. Does anyone know if it's possible to build this level of functionality into excel through VBA etc

excel
Formulas and Functions
Macros and VBA

1 Reply

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Sep 09, 2025

    Here the concept would like to share:

     

    1. Grid Setup:
      • Use a worksheet to represent each bay as a grid (e.g., Bay1, Bay2, Bay3)
      • Each cell represents a location in the bay
    2. Plate Movement Table:
      • A table with columns: Plate ID, Move Date, Bay, X, Y
      • This is your input data
    3. VBA Logic:
      • A macro that reads the table and places each plate in the correct cell
      • Use cell coloring or shapes to represent plates
      • Check for duplicate coordinates on the same date to flag clashes

     

    Sample:

    Sub UpdateBayLayout()
    Dim wsInput As Worksheet, wsBay As Worksheet
    Dim lastRow As Long, i As Long
    Dim plateID As String, moveDate As Date, bayName As String
    Dim x As Integer, y As Integer
    Dim cellKey As String
    Dim clashDict As Object
    Set clashDict = CreateObject("Scripting.Dictionary")

    Set wsInput = Sheets("Input")
    lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        plateID = wsInput.Cells(i, 1).Value
        moveDate = wsInput.Cells(i, 2).Value
        bayName = wsInput.Cells(i, 3).Value
        x = wsInput.Cells(i, 4).Value
        y = wsInput.Cells(i, 5).Value

        Set wsBay = Sheets(bayName)
        cellKey = bayName & "_" & x & "_" & y & "_" & moveDate

        If clashDict.exists(cellKey) Then
            wsBay.Cells(y, x).Interior.Color = RGB(255, 0, 0) ' Red for clash
        Else
            clashDict.Add cellKey, plateID
            wsBay.Cells(y, x).Interior.Color = RGB(0, 255, 0) ' Green for OK
        End If
    Next i
End Sub

     

    Clash Detection Logic

    • Use a dictionary or array to track occupied cells by date
    • If a cell is already occupied on that date, flag it as a clash
    • You can even pop up a message box or log clashes in a separate sheet

Resources