Making a cell mandatory based on previous cell entry

I am looking to achieve the following:

Column H has 5 potential inputs from a data validation list:

Pass, High, Medium, Low & N/A

If Column H is either High, Medium or Low, I need Column I to immediately demand a comment by way of a dialogue box.

How can I set this up?

It may also be beneficial to add that I know nothing about script/code writing nor do I understand any "Excel jargon", I am a pretty basic user & everything I know is self taught using trial & error!

Solution 1:

Based on my understanding of your question, I suggest a VBA Solution. In this example the sample column range is H2:H7 and your intended Validation is already in place for this range.

In your worksheet, press ALT + F11 to access VBA Editor. Under Left Pane see Microsoft Excel Objects. Below that double click the intended worksheet to open the Even Driven programming window. From the first drop down select Worksheet then from the second drop down select Change. This is Worksheet Change Event. The following lines will be displayed in the code editor.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Put the following code between these two lines.

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
    Exit Sub
End If

Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("H2:H7"))
If isect Is Nothing Then

Else

 If Target.Value = "High" Or Target.Value = "Medium" Or Target.Value = "Low" Then
     com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)

     Do While comm1 = ""
        comm1 = Application.InputBox(prompt:=com, Type:=2)
        On Error GoTo myloop
        If comm1 = False Then
            comm1 = ""
        End If
myloop:
     On Error GoTo -1
     Loop
     Target.Offset(0, 1).Value = comm1

 Else
    Target.Offset(0, 1).Value = ""  'Remove this line if not desired
 End If

End If

Save the file as Macro Enabled Excel workbook. Back in the worksheet. Test this code by selecting the intended values from the Validation List Box and see if it works for you and meets your requirment. This code may not have extensive error checking though.

One small thing I have added is later if you change the status to Pass the comment is removed from the column I.