Using VBA to auto populate cells based on user input (without using formulas if possible)

Hello

Windows Server 2005
Excel 2003

This is based on the same spreadsheet I used in another thread post. I'm trying to create a spreadsheet where somebody who's computer illitarate can type a product code in column B and it'll auto populate column F and G all done in VBA without using forumlas within the spreadsheet itself.

Reason being is that I'm under a strict file size limit and have to keep it as small as possible so having a forumla in one cell and dragging it down isn't the best option sorry.

I tried to create a VBA code which would auto populate the cells by placing the forumla into the cells when needed meaning I don't have to have 4000 rows of formulas sitting there waiting to be used (if that makes sense) but I couldn't get it to work it just returns a value of 0 Also it only works on the one cell not he cells adjasent to each other (sorry I'm explaining that poorly)

Also this process has to be done automatically without anybody resorting to macro buttons. Of course if anybody can come up with a method of doing this without inputting any forumals at all that'll be grand :D

Any help would be very apprechiated thank you

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngChange As Range, isect As Range
    Dim r As Integer
 
    If Target.Columns.Count = 256 Then Exit Sub
    
    Application.EnableEvents = False
    
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    Set rngChange = Range("B4:B" & LastRow)
    Set isect = Intersect(Target, rngChange)
    
    If Not isect Is Nothing Then
        For RowNo = Target.Row To Target.Row + Target.Rows.Count - 1
            Set Target = Cells(isect.Row + r, isect.Column)
            If Target = "" Then
                Cells(Target.Row, 6) = ""
                Cells(Target.Row, 7) = ""
            Else
                Cells(Target.Row, 6) = "=INDEX(Codes!$B$2:$B$1499,MATCH(Register!$F$4,Codes!$A$2:$A$1499,0))"
                Cells(Target.Row, 7) = "=INDEX(Codes!$C$2:$C$1499,MATCH(Register!$G$4,Codes!$A$2:$A$1499,0))"
            End If
            r = r + 1
        Next
    End If
    
    Application.EnableEvents = True
    
End Sub