How to verify information is entered in cell only if adjacent cell is not blank. Excel VBA

Question

I have two columns, one(B) with a name and the other (A) with a number. I am trying to make it so an error message will prevent me from closing excel if cells in column A are blank but the adjacent cells in column B are not blank.

For Example, if cell B2 is populated with a name, then the adjacent cell A2 MUST also have a number to go with it...otherwise the user will be prevented from closing excel.

I would also like the cells that are missing information in column A to be highlighted red.

The idea is to prevent users from not entering the information in column A where there is a name present in column b.

Could anyone help me with this?

Thanks

Answer 1

You can put this in the ThisWorkbook module in the Project:

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

Before you close a sheet, it'll check the range A1:A[last row in col. B] and see if any cells in column A are empty where the column B is not. It will then messagebox you the address to enter info. in.