Jun 01 2021 05:57 AM
Jun 01 2021 06:20 AM
SolutionStart by entering -1000000 in M10 and 1000000 in M12. These are initial values that will be overwritten.
Enter the formula =NOW() in another cell (it doesn't matter where).
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("M10").Value = Application.Max(Range("L9,M10"))
Range("M12").Value = Application.Min(Range("L9,M12"))
Application.EnableEvents = True
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook.
Make sure that you allow macros when you open it.
Jun 01 2021 08:27 PM
@Hans Vogelaar Very Very thanks sir, You are super, its working,
Jun 01 2021 09:37 PM
Another way to do this without code is to enable iterative calculations.
File | Options | Formulas tab | Calculation Options section | check the Enable iterative calculation checkbox and also change Maximum Iterations to 1.
Then in M10, enter the formula
=MAX(L9, M10)
Jul 14 2021 03:27 AM
Jul 14 2021 03:31 AM
What does the worksheet look like now?
Jul 14 2021 03:38 AM
Jul 14 2021 04:02 AM
Thank you. Change the code to
Private Sub Worksheet_Calculate()
Dim r As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
For r = 9 To 19
Range("M" & r).Value = Application.Max(Range("L" & r & ",M" & r))
Range("N" & r).Value = Application.Min(Range("L" & r & ",N" & r))
Next r
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Jul 14 2021 04:07 AM
Jul 14 2021 08:46 AM
Sir,
Have tried to modify and want to add timestamp whenever Max and Min will updated. The timestamp will also get updated accordingly.
But manual timestamp is working not the AutoMax and Min.
Please check the attached
Thanks in advance.
Jul 14 2021 12:35 PM
Private Sub Worksheet_Calculate()
Dim r As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
For r = 9 To 19
If Range("L" & r).Value > Range("M" & r).Value Then
Range("M" & r).Value = Range("L" & r).Value
Range("O" & r).Value = Now
End If
If Range("L" & r).Value < Range("N" & r).Value Then
Range("N" & r).Value = Range("L" & r).Value
Range("P" & r).Value = Now
End If
Next r
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
See the attached version.
Jul 14 2021 10:32 PM
Sir,
Thanks it is working. But finding 1 small problem.
Problem:-The value range is -1% to 1% in profit part. But the min value is always taking <=0,
1 small changes is requested.
Changes:-
If There is two different profit column
1 for Max profit and 1 for Min profit .
how the changes will be?
Please find attached for your reference
Thanks
Jul 15 2021 02:38 AM
Try this version:
Private Sub Worksheet_Calculate()
Dim r As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
For r = 9 To 19
If Range("L" & r).Value <> "" Then
If Range("M" & r).Value = "" Then
Range("M" & r).Value = Range("L" & r).Value
Range("O" & r).Value = Now
ElseIf Range("L" & r).Value > Range("M" & r).Value Then
Range("M" & r).Value = Range("L" & r).Value
Range("O" & r).Value = Now
End If
End If
If Range("K" & r).Value <> "" Then
If Range("N" & r).Value = "" Then
Range("N" & r).Value = Range("K" & r).Value
Range("P" & r).Value = Now
ElseIf Range("K" & r).Value < Range("N" & r).Value Then
Range("N" & r).Value = Range("K" & r).Value
Range("P" & r).Value = Now
End If
End If
Next r
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Jul 15 2021 03:17 AM
Jul 15 2021 03:34 AM
Jul 15 2021 03:51 AM
How would that work? Once you have a Min value you would never be able to set the Max value anymore, since that would make the Max timestamp later than the Min timestamp.
Jul 15 2021 04:14 AM
Jul 15 2021 05:23 AM
I'd leave it as it is now.
Jul 18 2021 11:01 PM - edited Jul 18 2021 11:08 PM
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target >= 1 / 100 Then
Range("B" & Target.Row).Clear
End If
End If
End Sub
Sir how to change it to formula in "A"
only manual typing is clearing cell B.
want to clear B when A cell change from other reference cell.
something like below
Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target >= 1 / 100 Then
Range("B" & Target.Row).Clear
Application.EnableEvents = True
End Sub
but its not working
Jun 01 2021 06:20 AM
SolutionStart by entering -1000000 in M10 and 1000000 in M12. These are initial values that will be overwritten.
Enter the formula =NOW() in another cell (it doesn't matter where).
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("M10").Value = Application.Max(Range("L9,M10"))
Range("M12").Value = Application.Min(Range("L9,M12"))
Application.EnableEvents = True
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook.
Make sure that you allow macros when you open it.