I want to run a check to see if all the cells in a range are empty, if they are then exit sub

Edit * I want to create a Workbook_BeforeClose event that checks for three different criteria. The first checks a range (L5:L63) to see if there is any data in it. The second checks to see if there is any data in cell (L65). The third checks to see if there is any data in cell (L66). If (L5:L63) has ANY cells that are not empty, then (L65) AND (L66) must have data entered in them prior to the workbook closing. If (L5:L63) has ANY cells that are not empty, and EITHER (L65) or (L66) are empty, then I want to call a message box to address which cell needs to have data input. If (L5:L63) has ANY cells that are not empty and BOTH (L65:L66) are empty, I would like to call a message box that tells the user to fill out both (L65 and L66).* If all of the above criteria are met, I want to exit the sub, no messages, able to close the workbook. I have added a few formulas on another worksheet within the workbook that allow me to better check that all of my conditions are met. Attached here: I am leaving the original message attached below:* I have a Workbook_Before Close sub that has three different criteria. First it is supposed to check if all the cells in a range are empty (L5:L), if they are then I want it to exit the sub. If ANY cell in the range is not empty, but two specific cells (L65 and L66) are empty, it should return a message box telling the user that the two cells need to have values in them (or one cell depending on if one or the other are empty). If you try to close the workbook with any cell in range (L5:L) with data in it, it should stop you from closing it until either a) the data in (L5:L) is cleared, or b) data is input into cells L65 AND L66. Right now it is allowing the workbook to be closed when the range is not empty and the two cells are.

Below is the code I have tried.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim rngCell As Range
Dim lngLstRow As Long

If ActiveSheet.Name = "Instruction & Data Input" Then

    lngLstRow = ActiveSheet.UsedRange.Rows.Count

    For Each rngCell In Range("L5:L" & lngLstRow)

        If rngCell.Value = "" Then
Exit Sub
              Exit For
               End If
                           Next
                           
       For Each rngCell In Range("L5:L" & lngLstRow)

        If rngCell.Value <> "" And rngCell.Offset(2, 0) = "" Then
            
            MsgBox ("Please add battery terminal  connection (+) resistance reading to sheet in yellow highlighted cells. This reading is taken from the incoming charger controller connection to the first battery terminal lug. ")
            
        
               Exit For
               End If
                           Next
            
        For Each rngCell In Range("L5:L" & lngLstRow)
            
            If rngCell.Value <> "" And rngCell.Offset(3, 0) = "" Then
                       
            MsgBox ("Please add battery terminal  connection (-) resistance reading to sheet in yellow highlighted cells. This reading is taken from the last battery to the out going charger controller connection.")

                               
                      
            Exit For
         End If
         Next
        
     
End If
End Sub

Solution

Try this out:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim c As Range, rng As Range, lngLstRow As Long, ws As Worksheet, v, stopClose As Boolean, colOffset
    
    Set ws = ThisWorkbook.Worksheets("Instruction & Data Input") 'reference specific worksheet
    
    lngLstRow = ws.Cells(Rows.Count, "L").End(xlUp).Row 'last cell in Col L with content
    If lngLstRow < 5 Then Exit Sub  'range is empty?

    'check each cell in Col L used range
    For Each c In ws.Range("L5:L" & lngLstRow).Cells
        v = c.Value
        If Len(v) > 0 Then
            For Each colOffset In Array(2, 3) 'checking cells 2 and 3 columns to the right
                With c.Offset(0, colOffset)  'the cell to be checked
                    .FormatConditions.Delete 'clear any existing CF flag
                    If Len(.Value) = 0 Then  'empty?
                        AddCFFill .Cells(1), vbRed 'flag the cell
                        stopClose = True           'at least one cell with a problem
                    End If 'required cell is empty
                End With
            Next colOffset  'check next column
        End If
    Next c
    
    If stopClose Then  'any cells with a problem? Alert the user and cancel close
        MsgBox "Some required values are missing! (see red-filled cells)" & vbLf & _
               "...rest of message here describes what's missing"
        
        Cancel = True 'cancel the close
    End If
    
End Sub

'Add a fill to a cell/range using a FormatCondition
'  Allows to revert to any previous fill on deleting the CF
Sub AddCFFill(c As Range, clr As Long)
    With c.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
        .SetFirstPriority
        .Interior.Color = clr
    End With
End Sub

Edit - updated:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim c As Range, rng As Range, ws As Worksheet, v, stopClose As Boolean, msg As String
    Dim L65empty As Boolean, L66empty As Boolean
    Set ws = ThisWorkbook.Worksheets("Instruction & Data Input") 'reference specific worksheet
    
    If Application.CountA(ws.Range("L5:L63")) = 0 Then Exit Sub 'nothing to check
    
    ws.Range("L65:L66").FormatConditions.Delete 'clear any flags
    
    L65empty = Len(ws.Range("L65").Value) = 0
    L66empty = Len(ws.Range("L66").Value) = 0
    
    If (Not L65empty) And (Not L66empty) Then Exit Sub 'all ok
    
    'one or both are empty
    If L65empty And L66empty Then
        msg = "Please fill in L65 and L66"
        AddCFFill ws.Range("L65:L66"), vbRed
    ElseIf L66empty Then
        msg = "Please fill in L66"
        AddCFFill ws.Range("L66"), vbRed
    Else
        msg = "Please fill in L65"
        AddCFFill ws.Range("L65"), vbRed
    End If
    
     MsgBox "If any values are entered in L5:L63 then other values must be filled!" & _
            " (see red-filled cells)" & vbLf & vbLf & _
            msg
    Cancel = True 'cancel the close
    
End Sub