MAXIMUM & MINIMUM using vba

[hsandeep]

I am using below code to get MAXIMUM & MINIMUM values



from C2:C42 in F2:F42 (for MAXIMUM) & G2:G42 (for MINIMUM) when A1=1

from K2:K42 in N2:N42 (for MAXIMUM) & O2:O42 (for MINIMUM) when I1=1

from S2:S42 in V2:V42 (for MAXIMUM) & W2:W42 (for MINIMUM) when Q1=1

from AA2:AA42 in AD2:AD42 (for MAXIMUM) & AE2:AE42 (for MINIMUM) when Y1=1

Private Sub Worksheet_Calculate()
    If Range("A1").Value <> 1 And Range("I1").Value <> 1 And Range("Q1").Value <> 1 And Range("Y1").Value <> 1 Then Exit Sub
    Dim i As Long
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    
    If Range("A1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "C").Value <> 0 Then
             Cells(i, "F").Value = Application.Max(Cells(i, "F").Value, Cells(i, "C").Value)
             If Cells(i, "G").Value = 0 Then
                 Cells(i, "G").Value = Cells(i, "C").Value
             Else
                Cells(i, "G").Value = Application.Min(Cells(i, "G").Value, Cells(i, "C").Value)
            End If
            End If
        Next i
    End If
    If Range("I1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "K").Value <> 0 Then
             Cells(i, "N").Value = Application.Max(Cells(i, "N").Value, Cells(i, "K").Value)
             If Cells(i, "O").Value = 0 Then
                 Cells(i, "O").Value = Cells(i, "K").Value
             Else
                Cells(i, "O").Value = Application.Min(Cells(i, "O").Value, Cells(i, "K").Value)
            End If
            End If
         Next i
    End If
    If Range("Q1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "S").Value <> 0 Then
             Cells(i, "V").Value = Application.Max(Cells(i, "V").Value, Cells(i, "S").Value)
             If Cells(i, "W").Value = 0 Then
                 Cells(i, "W").Value = Cells(i, "S").Value
             Else
                Cells(i, "W").Value = Application.Min(Cells(i, "W").Value, Cells(i, "S").Value)
            End If
            End If
        Next i
    End If
    If Range("Y1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "AA").Value <> 0 Then
             Cells(i, "AD").Value = Application.Max(Cells(i, "AD").Value, Cells(i, "AA").Value)
             If Cells(i, "AE").Value = 0 Then
                 Cells(i, "AE").Value = Cells(i, "AA").Value
             Else
                Cells(i, "AE").Value = Application.Min(Cells(i, "AE").Value, Cells(i, "AA").Value)
            End If
            End If
         Next i
    End If
    
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    
End Sub

The problem is the code gives the result but makes the workbook ‘hanged’ (Excel is calculating/processing fiercely).



Can someone please help to modify the code OR provide a new code altogether so that the above problem is solved.

 

[Joe4]

Your code does just fire "anytime C2:C42 value changes", it fires anytime any value anywhere on the sheet changes!
With using "Worksheet_Calculate", you cannot specify it to only run when certain values change. It runs whenever ANY value changes.
That is why I said it may be overkill.

What exactly is in cells C2:C42?
Are they formulas or hard-coded values?
How are values in this range updated?
If they are formulas, what do the formulas look like?

If they are hard-coded values being updated manually, you should DEFINITELY be using a Worksheet_Change event procedure instead, in which you can limit it to run only when a value within the range C2:C42 changes.

 

[hsandeep]

What exactly is in cells C2:C42? Formulas
Are they formulas or hard-coded values? Formulas
How are values in this range updated?
If they are formulas, what do the formulas look like? Example: formula in C2 is "=D2" & formula in D2 is linked to a cell in another worksheet which receives real time feed & gets constantly updated / changed.

 

[Joe4]

OK, so the data is real-time feeds, meaning it is being updated constantly. So I now understand your need for using Worksheet_Calculate.
However, if your data is being updated constantly in real-time, that would mean that this macro is probably also running constantly in real-time.

If I were you, I think I might try to enact some sort of controls, to limit how often these links are updated in the cells.
Otherwise, I don't really see any way out of your current predicament. It seems to be the "nature of your beast".
If your data is being updated constantly, why are you surprised that this VBA code is running constantly? That is expected behavior.