VBA - Is it possible to have an error message that automatically pops-up based on condition?

Question

If the users fill in the serial no. column in col B (it doesn't have to be all 10 of them, as long as one is filled), they need to fill up the other columns from col C to col F. Hence, if col B is filled up but any of the cells in col C to F are not filled up, I want an error message to pop up. I hope the image below gives a clearer idea..:

I'm not sure if Worksheet_SelectionChange will do what I want to accomplish...because I don't want to include a command button. As some users may not bother clicking on the command button to verify their inputs. This is the code I have at the moment, please feel free to advise accordingly....thank you:)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Range("B4").Value = "" Then
 MsgBox "serial no. is a Mandatory field", vbExclamation, "Required Entry"
 Range("B4").Select
 End If

 If Range("B4:B") <> "" Then
 If Range("C4:C").Value = "" Then
 MsgBox "Product is a Mandatory field", vbExclamation, "Required Entry"
 Range("C4:C").Select
 End If

' Adding values from sheet 2 for fruits drop-down list.
If Not Intersect(Target, Range("D3")) Is Nothing Then

Sheets("Sheet1").Range("D3") = "[Please Select]"

Dim col As New Collection
Dim rng As Range
Dim i As Long
Dim dvlist As String

'Loop thru the data range
For Each rng In Sheet2.Range("B2:B7")
'ignore blanks
    If Len(Trim(rng.Value)) <> 0 Then
        'create a unique list
        On Error Resume Next
        col.Add rng.Value, CStr(rng.Value)
        On Error GoTo 0
    End If
Next rng

'concatenate with "," as the delimiter
For i = 2 To col.Count
    dvlist = dvlist & col.Item(i) & ","
Next i

With Sheet1.Range("C2:C").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=dvlist
End With

End If

' Adding values from sheet 2 for country of origin drop-down list.
If Not Intersect(Target, Range("E4")) Is Nothing Then
Sheets("Screening Request").Range("E4") = "[Please Select]"

'Loop thru the data range
For Each rng In Sheet2.Range("A2:A7")
'ignore blanks
    If Len(Trim(rng.Value)) <> 0 Then
        'create a unique list
        On Error Resume Next
        col.Add rng.Value, CStr(rng.Value)
        On Error GoTo 0
    End If
Next rng

'concatenate with "," as the delimiter for list in Sheet 2
For i = 2 To col.Count
    dvlist1 = dvlist1 & col.Item(i) & ","
Next i

'add it to the DV
With Sheet1.Range("D3").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=dvlist1
End With

End If


 ' This is for the date (YYYYMMDD) column. I need it to be in YYYYMMDD format:
 If Not Intersect(Target, Range("F4:F13")) Is Nothing Then

       If Not IsNumeric(.Value) And Not cel.NumberFormat = "yyyymmdd" Then
            MsgBox "Date format must be in YYYYMMDD"
            cel.Value = ""
            Exit Sub
       Else: cel.NumberFormat = "yyyymmdd"
       End If
  End With

  End If

Answer 1

In general, you are making life much too hard for yourself. Use the tools that Excel provides (and there are many); you do not need to re-invent the wheel.

For example the lists for fruits and country of origin in your Sheet2 should be used as a list for data validation purposes in Sheet1 (Data Tab, Data Tools, Data Validation). Choose Allow List, make sure Ignore blank and In-cell dropdown are checked and select the range from Sheet2.

Similarly you can use data validation to validate dates in your last column.

You now do not need to validate these columns yourself, as they will always have blanks or valid values.

Combine this with my suggestion of conditional formatting (eg for the range c4:c13 you should enter =AND(B4<>"",ISBLANK(C4)) and for all three columns, you can produce a very simple verification routine. Something like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Cancel = MissingEntries()

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Cancel = MissingEntries()

End Sub

Private Function MissingEntries() As Boolean

Dim i As Integer
Dim j As Integer
Dim atLeastOneLine As Boolean

    atLeastOneLine = False
    For i = 4 To 13
        If (Cells(i, 2) <> "") Then
            atLeastOneLine = True
            For j = 3 To 6
                If Cells(i, j) = "" Then
                    MsgBox ("Please supply values for highlighted cells")
                    MissingEntries = True
                    Exit Function
                End If
            Next
            If WrongSerialNumber(i) Then
                MissingEntries = True
                Exit Function
            End If
        End If
    Next
    If Not atLeastOneLine Then
        MsgBox ("Please supply values for at least one line")
        MissingEntries = True
    Else
        MissingEntries = False
    End If

End Function

Private Function WrongSerialNumber(i As Integer) As Boolean

    Dim yr As Integer
    Dim serialNo As String
    Dim yrStr As String
    Dim yrCell As String


    serialNo = Cells(i, 2)
    If Len(serialNo) < 3 Then
        WrongSerialNumber = True
        MsgBox "Serial Number for row no. " + CStr(i - 3) + " is too short.  Please correct."
        Exit Function
    End If
    yrCell = Cells(i, 6)

    If Len(yrCell) = 8 Then

        yr = CInt(Left(Cells(i, 6), 4))
        If yr > 1968 Then
            If Mid(yrCell, 3, 2) <> Mid(serialNo, 2, 2) Then
                WrongSerialNumber = True
                MsgBox "Serial Number for row no. " + CStr(i - 3) + " has wrong second and third digits.  These should match the third and fourth digits of the date.  Please correct."
                Exit Function
            End If
        End If
    End If
    WrongSerialNumber = False

End Function

Note that I validate on both close and save. The former is optional.

Because of the highlighting, a simple message suffices, you are spared the work of informing the user, which cells are missing. In this way the combination of in-built Data Validation and Conditional Formatting makes the remainder of your task so much easier.