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
Bookmarks