color cell in red, when entering empty time value

[robertvdb]

I have a range of cells in column B and C, where the user has to enter some time values. If he accidentally enters an empty value, then this is trapped by the below code. However, I want to add some color as long as the user does enter empty values.

Thanks for any help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim col_B_rng As Range
Dim col_C_rng As Range

Dim StartTime As String
Dim EndTime As String

Dim startNum As Integer
Dim endNum As Integer

Set col_B_rng = Range("B2:B4")
Set col_C_rng = Range("C2:C4")


If Target.Count = 1 And Not Application.Intersect(Target, col_B_rng) Is Nothing Then

    StartTime = Target.Value
    Application.EnableEvents = False
        
    If StartTime = "" Then
        Target.Select
        MsgBox ("Empty value.  Try again."), vbCritical
        Application.Undo
        Application.EnableEvents = True
        Exit Sub
    Else
        StartTime = Format(Target.Value, "0\:00")
        Target.Offset(0, 1).Select
    End If
End If

 

[6StringJazzer]

Why not use a conditional formatting rule applied to B2:B4 to apply a fill color when the cell is blank:

=B2=""

Also your code defines col_C_rng but never uses it. Did you mean to include those cells too?

If Target.Count = 1 And (Not Application.Intersect(Target, col_B_rng) Is Nothing Or Not Application.Intersect(Target, col_C_rng) Is Nothing) Then

 

[robertvdb]

Thanks. I only posted part of my code, Col C is used further on.

I am not very pro conditional formatting...