Hi i am not sure if putting a formula in the cell is causing the below code preventing it from running, before anymore details please take a look at the code below which is working perfectly fine after the code i will explain what it does...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nMin As Long, nMax As Long

'Intersect(Target.Cells, Range("1:50"), Union(Columns("A"), Columns("E")))
If Intersect(Target.Cells, Range("1:50"), Union(Columns("A"), Columns("D"))) Is Nothing Then Exit Sub
'Replace line above with line below to work on any column
'If Target.Row <> 1 And Target.Row <> 4 Then Exit Sub

With WorksheetFunction
    nMax = Cells(Target.Row, "B").Value
    nMin = Cells(Target.Row, "C").Value
    If Target.Column = 4 Then
        nMax = .Max(Target.Value, Cells(Target.Row, "A").Value)
        nMin = .Min(Target.Value, Cells(Target.Row, "A").Value)
    End If
    Cells(Target.Row, "B").Value = .Max(Target, nMax)
    Cells(Target.Row, "C").Value = .Min(Target, nMin)
End With

End Sub
ok basically this code makes sure that whenever there is a change in A column for range of A1 to A27 it will take a mean value from D column and then the max value will be placed inside the respective B column and minimum value will be placed in C.
Run the code for your self to get a better understanding if you are a forex trader you will understand it faster that's the only benefit.
For those pro's and administrators who understand this code well, here is my question.
There is a change happening whenever there is a change of value in A and the code runs perfectly fine. Problem is when i apply this formula in A 1 lets say A1's formula is =F1.
So by logic when ever there is a change in F1 there will be a change in A1 too which is good, but according to the code there should be a change occurring in B and C as well but there is no change its like the code is never being initiated