VBA for error message to prompt if cell is not filled when there is text inside another cell

[SaraWitch]

Hello all,

I am after a VBA that displays a message box to prompt the user to complete another cell dependant on the Data Validation List option, i.e. if 'Cancelled by tenant' is selected from the DVL in column H, then column AC also needs to be completed (by selecting 'Cancelled' from that DVL). I want to be able to do this for several choices from the first DVL (e.g. 'Cancelled by LA', 'Cancelled by RSL', 'Completed', 'Refused'). I want to apply this to the whole of the columns (row range 9:568). Oh, and I already have conditional formatting applied (quite a bit, so I wanted an actual message box!). I do have a DV Input Message, but some people still ignore that too!! ? Hence the pop-up message box!

Not sure it's possible with everything I already have set up in this workbook, but any advice would be greatly received!

Ta muchly, folks!
Sara

 

[DanteAmor]

Carrying out these types of checks on the sheet could be difficult.
It is recommended to capture the data in a UserForm, validate and until the data is correct and complete, then pass the data to the sheet.
In any case, validations (DVL) or UserForm there are advantages and disadvantages.

I leave you a code for the events of your sheet.
If you capture a data in column H, check if there is data in AC, if it does not exist in AC, then select cell AC and it does not allow you to select another cell until you capture a data in AC.

Option Explicit

Dim needData As String

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim msg As Boolean
  
  If Not Intersect(Target, Range("H9:H568")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Range("AC" & Target.Row).Value = "" Then msg = True
  End If
  '
  If Not Intersect(Target, Range("AC9:AC568")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then
      If Range("H" & Target.Row).Value <> "" Then msg = True
    End If
  End If
  '
  If msg Then
    MsgBox "cell is not filled"
    Application.EnableEvents = False
    Range("AC" & Target.Row).Select
    Application.EnableEvents = True
    needData = Range("AC" & Target.Row).Address
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If needData <> "" Then
    If Range(needData).Value <> "" Then
      needData = ""
      Exit Sub
    End If
    If ActiveCell.Address(0, 0) = Range(needData).Address(0, 0) Then
      Exit Sub
    End If
    MsgBox "cell is not filled"
    Application.EnableEvents = False
    Range(needData).Select
    Application.EnableEvents = True
  End If
End Sub

Note: it would be more practical to perform these validations in a UserForm.