Excel, if statement and index in code

[brickwall015]

Hello

I am trying to create a code that will either allow the user to input a value, or INDEX a value depending on the input in the column before. I have figured out how to allow the user to input the value, thanks to some one else on this Forum, but I can't seem to get the INDEX formula right.

As of now, this is my code:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D440")) Is Nothing Then Exit Sub
If Cells(Target.Row, "D") = "INPUT WALL" Then
Cells(Target.Row, "E") = " "
Cells(Target.Row, "E").Interior.ColorIndex = 15

Else
Range(Target.Row, "E").Formula = "=IF(B4=0,0, INDEX(Piping!$B$2:$AC$27,MATCH(C4,Piping!$B$2:$B$27,),MATCH(D4,Piping!$B$2:$AC$2,)))"
Cells(Target.Row, "E").Interior.ColorIndex = 2
End If
End Sub

the INDEX however is only looking at C4 and D4 for the whole range from 4 to 40.

I want it to be like a normal formula you put into a cell and drop the box down. So that when I am in row 5, it is indexing c5 and D5, and when I am in row 6, it is indexing C6 and D6, and so on. because as of now, no matter what row I am in, it still index's C4 and D4. But I only want this to happen if "INPUT WALL" is not selected in column D for that particular Row

[dglienna]

try "$"&D&6