replace number if greater than

[diggs]

I have a spreadsheet that track safety data. One cell has the number of days
since an accident. It updates automatically. The other cell is the past best
number of days without an accident. I want the Past Best cell to replace its
value if the current days since accident exceeds its value. Does anyone have
any suggestions?

 

[Bob Phillips]

It would be easy with a worksheet change event.

Which two cells are these numbers in?

 

[diggs]

B68 is the current
H68 is the past best

It would be easy with a worksheet change event.

Which two cells are these numbers in?

--
__________________________________
HTH

Bob

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B68"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value > Me.Range("H68").Value Then

Me.Range("H68").Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[diggs]

Thanks it worked

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B68"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value > Me.Range("H68").Value Then

Me.Range("H68").Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob