Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub Workbook_BeforeClose(Cancel As Boolean)
- Dim lastRow As Long
- Dim ws As Worksheet
- Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change this to the sheet you need to check!
- lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
- Dim rng As Range, cel As Range
- Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(lastRow, 2))
- For Each cel In rng
- If cel.Offset(0, -1).Value = "" And cel.Value <> "" Then
- MsgBox (cel.Address & " is empty. Please populate before closing file.")
- cel.Offset(0, -1).Interior.Color = RGB(255, 0, 0)
- Cancel = True
- ' Exit Sub
- End If
- Next cel
- End Sub
Add Comment
Please, Sign In to add comment