VBA that updates a range as I insert new rows

[cameronnicol]

Hello,

The below VBA code populates a series of cells in a table, using A2 to update the file path. Column A contains an order number. It will also generate an error message if the fil path specified does not exist.

I would like the range "ordnum" to update as users insert new rows into the table. So if someone put an order number in A3 the file path would update.

Any help would be much appreciated, I'm completely stuck and a bit of a noob.

Cheers.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then 'check if A2 is the cell that changed
ordnum = Range("A2")
If ordnum = "" Then Exit Sub 'check if order number is blank if so exit sub

'Display error message and change cell colour if folder does not exist.
If Not FileFolderExists("P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP") Then
        MsgBox "Folder does not exist!"
        Range("A2").Interior.Color = RGB(230, 0, 0)
        Exit Sub
Else: Range("A2").Interior.Color = RGB(0, 230, 0)
    End If
    Range("B2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Order and Response Details'!$B$2" 'Client
    Range("E2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Order and Response Details'!$B$3" 'Site Size
    Range("H2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Order and Response Details'!$B$4" 'Turnaround
    Range("I2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Order and Response Details'!$E$3" 'Ordered date
    Range("J2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Order and Response Details'!$E$5" 'Due date
    Range("L2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Time Spent'!$AK$10" 'Completed by
    Range("P2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Order and Response Details'!$B$5" 'Estimated drawing hours
    Range("S2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Time Spent'!$AW$11" 'GDC time
    Range("T2").Formula = "='P:\GBBSB\Telecoms\Fixed\NRSWA\Geospatial\01_Products\" & ordnum & "\01_WIP\[" & ordnum & " Production Checklist.xlsx]Time Spent'!$AW$12" 'UK time
End If
End Sub