title: Excel VBA Dropdown-menu
body: <p>I have some VBA code triggered at cell changes.
  One part of the code creates a dropdown in Column C if there are value in Column A on the same row.
  There are two things with the dropdown code that I would like to get some help with.</p>
  
  <p>First: If I change the dropdown value for some rows to Yes (there are by default set to No) and then add new value in Column A all dropdown:s changes there values to No. I would like to add some kind of check like that If the value is set to Yes don´t change it back.</p>
  
  <p>Second: Seems like some kind of bug so I will post all my code in case something else might cause it.
  Let´s say I have value added in Column A for five rows and I delete them (It doesn´t mather if I delete them one by one or all five at the same time nor the order I delete them). The header in Column C get´s a dropdown after last value in Column A is deleted (Note that Column A also has a header which isn´t deleted.</p>
  
  <p><a href="https://i.stack.imgur.com/O2eno.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/O2eno.jpg" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/zmvzJ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zmvzJ.jpg" alt="enter image description here"></a></p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False 'to prevent endless loop
    On Error GoTo Finalize 'to re-enable the events
  
    For Each columnAcell In Target.Cells
        columnAcell.Offset(0, 3) = Mid(columnAcell, 2, 3)
        If IsEmpty(columnAcell.Value) Then columnAcell.Offset(0, 4).ClearContents
        If IsEmpty(columnAcell.Value) Then columnAcell.Offset(0, 2).Clear
        If IsEmpty(columnAcell.Value) Then columnAcell.Offset(0, 1).Clear
        If IsEmpty(columnAcell.Value) Then columnAcell.Offset(0, 5).Clear
    Next
  
    Application.ScreenUpdating = False
  
    Dim w1 As Worksheet, w2 As Worksheet
    Dim c As Range, FR As Variant
  
    Set w1 = Workbooks("Configure Accesspoints.xlsm").Worksheets("AP_Input")
    Set w2 = Workbooks("Configure Accesspoints.xlsm").Worksheets("Data")
  
    For Each c In w1.Range("D2", w1.Range("D" &amp; Rows.Count).End(xlUp))
        FR = Application.Match(c, w2.Columns("A"), 0)
        If IsNumeric(FR) Then c.Offset(, 1).Value = w2.Range("B" &amp; FR).Value
    Next c
  
    For Each c In w1.Range("D2", w1.Range("D" &amp; Rows.Count).End(xlUp))
        FR = Application.Match(c, w2.Columns("A"), 0)
        If IsNumeric(FR) Then c.Offset(, 2).Value = w2.Range("D" &amp; FR).Value
    Next c
  
    Dim myList As String, r As Range
  
        myList = "Yes,No"
  
    For Each r In w1.Range("A2", w1.Range("A" &amp; Rows.Count).End(xlUp))
        If r.Value &lt;&gt; vbNullString Then
           With r.Offset(, 2).Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
           End With
           r.Offset(, 2).Value = Split(myList, ",")(1)
        End If
    Next r
  
  Finalize:
    Application.EnableEvents = True
  End Sub
  </code></pre>