CODEWITHSUNDEEP
excelvba
Jackie Chua
Jackie Chua

Reputation: 25

Unable to select a range of cell

I am new to here so I'm not sure the rules that well. I currently stumbling at a code that I can't seem to know the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Format(Range("H4").Value, "HH:MM:SS") = "00:15:00" Then
        MsgBox "Block 3 ends in 15 mins"             
    End If
End Sub

The code is able to handle a single cell but when I change the cell "H4" to "H2:H7" it will not work. Please help and thank you for the advance.

Upvotes: 1

Views: 42

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

Of course there is not one .Value for multiple cells H2:H7 but multiple values.

Therefore you will need to test each value on its own in a loop. And you need to decide if all of the cells need to meet the If criteria or only one of them.

This is an Example for a massage box whenever a value in the range H2:H7 is 00:15:00.
Note that this runs on every change of any cell.

Option Explicit

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 3 ends in 15 mins"
        End If
    Next c
End Sub

If you want that only to run when a cell in Range("H2:H7") is changed use

Option Explicit

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

If you want to test only the changed cell (not the whole range) use

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H2:H7")) Is Nothing Then
        If Format$(Target.Value, "HH:MM:SS") = "00:15:00" Then
            MsgBox "Block 3 ends in 15 mins"
        End If
    End If
End Sub

Upvotes: 1

Related Questions