Display MsgBox if cell value changes and is not blank

Question

I have a range (merged cell) M5:O5 that needs to trigger a MsgBox as follows:

• If range is edited and is not null
• If range is deleted to a null value then no MsgBox
• If range is selected and box is empty or has data then no MsgBox

What I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim billtoanswer As Integer
   'If IsNull(Range("M5:O5")) Then  <-- other stuff I was trying
   'End
       If (Application.Intersect(Range("M5:O5"), Target) Is Nothing) Then
       End If
       billtoanswer = MsgBox("Use this address for Bill To?", vbYesNo + vbQuestion, "Bill To?")
       If billtoanswer = vbYes Then
       Range("F26").Value = Range("M4").Value & ", " & Range("M5").Value
       End If
   End If
End Sub

Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20190) 64-bit

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim billtoanswer As Integer
   
   If (Application.Intersect(Range("M5:O5"), Target) Is Nothing) Then
      ' Skip
   Else
      If Trim(CStr(Target.Text)) = "" Then
         ' Skip
      Else
         billtoanswer = MsgBox("Use this address for Bill To?", vbYesNo + vbQuestion, "Bill To?")
         If billtoanswer = vbYes Then
            Range("F26").Value = Range("M4").Value & ", " & Range("M5").Value
         End If
      End If
   End If
End Sub