Hi All,

see code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("E41:E" & 40 + Range("A37"))) Is Nothing Then
        MsgBox Replace(UCase(Target.Address), "\x36.\x36", "$G$", , , vbTextCompare) ' < THIS LINE
        Application.ScreenUpdating = False
        Range(Replace(UCase(Target.Address), "E", "G")) = "=RC[-1]*(1+RC[-2])"
        Range("G41:G" & 40 + Range("A37")).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End If

End Sub
Basically, say my range that is selected (or Target.Address) is "$D$46:$E$48, $D$50:$E$50" , and obviously the macro only really runs when there is a change in "E", what i want my macro to do is change what ever is between the $ signs to "G" and i've tried using the replace method, but that doesnt seem to work.

Could some one please help.

Thank you

Claudio