How to Make a Cell Mandatory to Complete When Another Cell Has Changed?

To achieve the goal, I would like to suggest two possible methods.

Method 1, Data Validation:

• Situation 1, when Cell L6 is filled with either Yes or No.

• Situation 2, when Cell L6 is filled with neither Yes nor No.

• Situation 3, when Cell L6 is Blank.

How it works:

1. Select cells L7 & L8 and from Data Tab click Data Validation.
2. Set Custom as Validation Criteria and enter this Formula.

=NOT(ISBLANK($L$6))*(OR($L$6="Yes",$L$6="No"))

3. Finish with Ok

Method 2, VBA (Macro):

• Right Click the Sheet Tab & hit View Code from the Poped up menu to get VB editor.

• Copy & Paste this Code as Standard Module with the Sheet.

  Option Explicit
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Not Intersect(Target, Range("L6")) Is Nothing Then
  
      If Target.Value = "Yes" Or Target.Value = "No" Then
          MsgBox "Now You Can fill L7 & L8"
      Range("L7").Activate
  
      ElseIf Target.Value <> "Yes" Or Target.Value <> "No" Then
          MsgBox "Target cell must have data Yes Or No,, to fill L7 & L8"
      Target.Activate
      End If
  
      If Target.Value = "" Then
      MsgBox "Target Cell Can't be Blank,,"
      Target.Activate
      End If
  End If
  
  End Sub
  

N.B.

• This code will start responding as soon you fill the Cell L6.

• You may adjust cell references in the Formula as well as in VBA code as needed.