Conditional Updates on cell comment|VBA & Macros|Excel Forum|My Online Training Hub

I'm excited to revise the script as below. It works as expected. The key to skip updating the comment if it meets the condition is highlighted in Green. Please note it must be executed before the line ' Update the max and min values if needed'. Copilot put it after finishing the process to ' Update the max and min values if needed. Please feel free to let me know if there is any room for further improvement. Thanks a ton. 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim commentText As String
Dim currentDate As Date
Dim existingMaxValue As Double
Dim existingMinValue As Double
Dim newValue As Double
Dim firstEntry As Boolean

' Set the target ranges where you want to track changes
Set rng = Union(Range("O15"), Range("O16"), Range("P15"), Range("P16"))

' Check if the changed cell is within the target ranges
If Not Intersect(Target, rng) Is Nothing Then
' Get the updated value
newValue = Target.Value
commentText = Format(newValue, "###,###")

' Get the current date
currentDate = Now

' Initialize the flag for the first entry
firstEntry = True

' Check if there's an existing comment
If Not Target.Comment Is Nothing Then
' Extract the values from the existing comment
Dim commentLines() As String
Dim i As Integer
commentLines = Split(Target.Comment.Text, vbNewLine)

' Initialize maxValue and minValue with the first comment value
If UBound(commentLines) >= 0 Then
Dim firstLineParts() As String
firstLineParts = Split(commentLines(0), " > ")

If UBound(firstLineParts) >= 0 And IsNumeric(firstLineParts(0)) Then
existingMaxValue = CDbl(firstLineParts(0))
existingMinValue = CDbl(firstLineParts(0))
firstEntry = False
End If
End If

' Find the existing max and min values
For i = LBound(commentLines) To UBound(commentLines)
Dim lineParts() As String
lineParts = Split(commentLines(i), " > ")

If UBound(lineParts) >= 0 Then
Dim numericValue As Double
If IsNumeric(lineParts(0)) Then
numericValue = CDbl(lineParts(0))
If numericValue > existingMaxValue Then
existingMaxValue = numericValue
End If
If numericValue < existingMinValue Then
existingMinValue = numericValue
End If
End If
End If
Next i

' Close the session if the new value in within the max/min range
If newValue >= existingMinValue And newValue <= existingMaxValue Then
Exit Sub ' Bypass comment update
End If

' Update the max and min values if needed
If newValue > existingMaxValue Then
existingMaxValue = newValue
End If
If newValue < existingMinValue Then
existingMinValue = newValue
End If

' Update the comment
commentText = commentText & " > " & Format(currentDate, "yyyy-mm-dd")
Target.Comment.Text Text:=Target.Comment.Text & vbNewLine & commentText & _
vbNewLine & "Max: " & Format(existingMaxValue, "###,###") & _
vbNewLine & "Min: " & Format(existingMinValue, "###,###")
Else
' No existing comment, add a new one
commentText = commentText & " > " & Format(currentDate, "yyyy-mm-dd")
Target.AddComment commentText
End If
End If
End Sub

Hi Hans, Thanks for your feedback. The file is attached here for your reference. Sheet1 encountered the problem I highlighted and the issue has been tackled in Sheet2 by adding the following lines:

If newValue >= existingMinValue And newValue <= existingMaxValue Then
Exit Sub ' Bypass comment update
End If

Let me repeat it one more time. it must be performed before the code starting ' Update the max and min values if needed. That's it. My Excel version is 2013 under window10. 

Please note my goal is NOT "to update the comment every time a new value is entered that is not already present in the comment" only. It needs to check whether the new value is within the max/min range, otherwise NO updating is to be done.