VBA Message box to appear if Cell value changes before a certain time.

[Dgenty]

Morning,

I need to get a message box to appear if a cell value changes from a "O" to a "C" before 17:00 on any given day. After this time the message box does not need to appear.

So basically the VBA equivalent of if A1 value = "C" and Time<="17,0,0" then message box "A1 =C"

Any help would be appreciated.

 

[BarryL]

try the below worksheet change event in the sheet object module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim datecheck As String
If Target.Address = "$A$1" Then
datecheck = Format(Date & " 17:00:00", "dd/mm/yyy hh:mm:ss")
If Format(Now(), "dd/mm/yyy hh:mm:ss") <= datecheck Then
Target.Value = "O"
End If
End If

End Sub

 

[Dgenty]

Hi Barry, This does not seem to bring a message box up if the value changes

 

[BarryL]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim datecheck As String
If Target.Address = "$A$1" Then
datecheck = Format(Date & " 17:00:00", "dd/mm/yyy hh:mm:ss")
If Format(Now(), "dd/mm/yyy hh:mm:ss") <= datecheck Then
msgbox "target value has changed"
End If
End If

End Sub