When there is a blank the code works exactly the way Xiao outlined it above and I quote " However, this will check only one at a time. So if there are blanks in D6:E6 and D20 as well, the message box will prompt with first message (i.e. CA Name cannot be blank) only."

However when all the specified ranges are not blank an error apppears

400


I see that the ranges set to be copied has been copied (highlighted). Can we assume the action or code stopped there then has an error.

Note: I tried using both codes: first by Roy's then Xiao's but has the same result.

Modified the code a bit to match the ranges. And changed the copying part of the code to simplify.

Sub IdentifyBlanksinDataValidation()
    Dim msg As String
    If Application.WorksheetFunction.CountBlank(Range("d6")) > 0 Then
        msg = "CA NAME cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d17")) Then
        msg = "Process Type cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d18")) Then
        msg = "Sub Type cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d20")) Then
        msg = "Product/Specific Type cannot be blank": GoTo Blank
    End If

    Range("R2:AE2").Copy
    Sheets("Raw_Data").Select
    Range("A5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Rows("5:5").Insert Shift:=xlDown
    Sheets("Tracker").Range("D10:D11,D13:D15,D17:D20,B23:F32").ClearContents

    Exit Sub
Blank:
    MsgBox msg, vbCritical, "Input missing"
End Sub