Excel Change cell value based on dropdown option selected-CodePudding

Cell F10 is a dropdown list that offers Yes, No or can be set to blank. This is made from a named list using Data Validation List with the source set to my named list.

When an option is selected in F10 or the cell is cleared I want to update H10.

If F10 = Yes then H10 should contain Allowed, If F10 = No or is empty H10 should be empty, but editable by the user.

I will need to copy this down to other rows on the same worksheet.

I've spent the last hour googling, but haven't found any way to do this.

Can someone advise how it can be done. Thanks

CodePudding user response：

Open the VBA editor and enter this code into the worksheet module (by default it's named Sheet1 (Sheet1)):

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    For Each cell In Target
      If cell.Column = 6 And cell.Row >= 10 And cell.Column <= 100 Then
        If cell.Value = "Yes" Then
          cell.Offset(0, 2).Value = "Allowed"
        Else
          cell.Offset(0, 2).Value = ""
        End If
      End If
    Next cell
    Application.EnableEvents = True
End Sub

Note that the comparison is case-sensitive - you may want to change it to If LCase([F10]) = "yes" Then ...