Private Sub Worksheet_Calculate()
Dim Cell As Range
' For each cell in the specified N column range
For Each Cell In Range("N5:N32")
' If the cell value is BUY then...
If Cell.Value = "BUY" Then
' If the adjacent cell does not end with Y) then...
If Right(Cell.Offset(0, 1), 2) <> "Y)" Then
' Update the adjacent cell with the date, time and (BUY)
Cell.Offset(0, 1).Value = Date + Time & " (BUY)"
End If
' Else if the cell value is SELL then...
ElseIf Cell.Value = "SELL" Then
' If the adjacent cell does not end with L) then...
If Right(Cell.Offset(0, 1).Value, 2) <> "L)" Then
' Update the adjacent cell with the date, time and (SELL)
Cell.Offset(0, 1).Value = Date + Time & " (SELL)"
End If
' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
ElseIf Cell.Value <> "BUY" & Cell.Value <> "SELL" Then
' Clear the adjacent cell
Cell.Offset(0, 1).Value = ""
End If
' Check next cell in specified range
Next Cell
' For each cell in the specified L column range
For Each Cell In Range("L5:L32")
' If the cell value is BUY then...
If Cell.Value = "BUY" Then
' If the adjacent cell does not end with Y) meaning the previous value was SELL then...
If Right(Cell.Offset(0, 1), 2) <> "Y)" Then
' Update the adjacent cell with the date, time and (BUY)
Cell.Offset(0, 1).Value = Date + Time & " (BUY)"
End If
' Else if the cell value is SELL then...
ElseIf Cell.Value = "SELL" Then
' If the adjacent cell does not end with L) meaning the previous value was BUY then...
If Right(Cell.Offset(0, 1).Value, 2) <> "L)" Then
' Update the adjacent cell with the date, time and (SELL)
Cell.Offset(0, 1).Value = Date + Time & " (SELL)"
End If
' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
ElseIf Cell.Value <> "BUY" & Cell.Value <> "SELL" Then
' Clear the adjacent cell
Cell.Offset(0, 1).Value = ""
End If
' Check next cell in specified range
Next Cell
End Sub