You could do a COUNTIFS and see if there's at least one data point.
Code
- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- Dim myCount As Long
- With Sheets("5s Report Dec 2019")
- 'How many items have fails but nothing in next column?
- myCount = WorksheetFunction.CountIfs(.Range("H:H"), "Fail", .Range("I:I"), "")
- End With
- If myCount > 0 Then
- Cancel = True
- MsgBox "Please enter a values in columns I and J", vbCritical, "Error!"
- End If
- End Sub