daily pastebin goal
59%
SHARE
TWEET

Untitled

a guest May 16th, 2018 105 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2. Dim lastRow As Long
  3. Dim ws As Worksheet
  4.  
  5. Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change this to the sheet you need to check!
  6. lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
  7.  
  8. Dim rng As Range, cel As Range
  9. Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(lastRow, 2))
  10.  
  11. For Each cel In rng
  12.     If cel.Offset(0, -1).Value = "" And cel.Value <> "" Then
  13.         MsgBox (cel.Address & " is empty. Please populate before closing file.")
  14.         cel.Offset(0, -1).Interior.Color = RGB(255, 0, 0)
  15.         Cancel = True
  16.         ' Exit Sub
  17.     End If
  18. Next cel
  19.  
  20. End Sub
RAW Paste Data
Top