Search code examples
excelmsgboxvba

Notify the text in the cell when the event is fired

I was able to manage to be notified when an event's circumstances is met.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Range("H2:H7")
    If Format$(c.Value, "HH:MM:SS") = "00:15:00" Then
        MsgBox "Block ends in 15 mins"
    End If
Next c

Now my current problems is that, when one of the event is triggered. I want to be notified by the MsgBox which Block is triggered.

Block   
1   15:00
2   17:00
3   19:00
4   21:00
5   23:00
6   01:00

For example like above, Block 2 hits 15 mins, I want to be notify by MsgBox "Block 2 ends in 15 mins". Thank you for the help and hope that I'm not confusing.

Solution

  • If the block number is one cell left of the tested cell use:

    MsgBox "Block " & c.Offset(0, -1).Value & " ends in 15 mins"