Thread: Cell Reference from Programming

Hi,
Hope you can help in resolving the below issue,
I have a code that works for Retaining the Upper (in B1) and Lower values (in C1) as they reach changed to next number in Cell A1 ------ while a mean Value is maintained in D1
--

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 After the above was developed with help of StephenR form this forum.
...
I need a new help as below.
..
I want to insert a new value in A1 as refrence from G1.
..
When I do this refrence in the Cell A1 directly the above code stopped working further .
..
Hope I was able to expalin the isse and reqeust you to help me in resolving the issue.

Hi Stepheen,
THanks for your update, yes you are correct I want same as you said.
Please help how I can proceed ..
...
And also I want to have refrence to D1 also in same way meaning d1=h1
..
Hope you can help me on this.

Hi,
Hope I Could get help today?
Thanks.
Baba

excelbaba;

Worksheet_Change() is only triggered when you manually change a cell or if a macro changes a cell. If a formula changes the value Worksheet_Change() is not triggered.
So you can do something like this:

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

        'Note these will trigger Worksheet_Change() again
        '    but since they are not in A or D the Intersects above will filter them out.
        Cells(Target.Row, "B").Value = .Max(Target, nMax)
        Cells(Target.Row, "C").Value = .Min(Target, nMin)

        'Add These
        'These will also trigger Worksheet_Change()
        '    so put a test to eliminate an endless loop.
        If Cells(Target.Row, "A").Value <> Cells(Target.Row, "G").Value Then
            Cells(Target.Row, "A").Value = Cells(Target.Row, "G").Value
            Cells(Target.Row, "D").Value = Cells(Target.Row, "H").Value
        End If
    End With
    
End Sub