title: Default Value From Drop Down List
body: <p>I wonder whether someone may be able to help me please.</p>
  
  <p>I'm using the code below, which among a number of actions being performed, automatically populates column "A" with the date, and column "AS" with the text value "No" when a new record is created within a Excel spreadsheet.</p>
  
  <pre><code>Option Explicit
  Public preValue As Variant
  Private Sub Worksheet_Change(ByVal Target As Range)
  
      Dim Cell As Range, res As Variant
      Dim rCell As Range
      Dim Rng1 As Range
      Dim Rng2 As Range
      Dim Rng3 As Range
  
  
      Application.EnableCancelKey = xlDisabled
      'Sheets("Input").Protect "handsoff", UserInterFaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True
  
      If Target.Column = 3 Then
          If Target = "No" Then MsgBox "Please remember to make the same change to all rows for " &amp; Target.Offset(0, -1).Value &amp; " and delete any future forecasts"
      End If
  
         If Target.Cells.Count &gt; 1 Then Exit Sub
      On Error Resume Next
  
      If Not Intersect(Target, Range("B5:AD400", "AF5:AQ400")) Is Nothing Then
          If Target.Value &lt;&gt; preValue And Target.Value &lt;&gt; "" Then
              Application.EnableEvents = False
             With Rows(Target.Row)
                      .Range("A1").Value = Date
                      .Range("AS1").Value = "No"
          End With
              Application.EnableEvents = True
              Target.Interior.ColorIndex = 35
          End If
      End If
  
      On Error GoTo 0
      If Target.Column = 45 Then
                  If Target.Value = "Yes" Then
                  Set Rng1 = Application.Union(Cells(Target.Row, "B").Resize(, 19), Cells(Target.Row, "R"))
                  Rng1.Interior.ColorIndex = xlNone
                  Set Rng2 = Application.Union(Cells(Target.Row, "S").Resize(, 12), Cells(Target.Row, "AD"))
                  Rng2.Interior.ColorIndex = 37
                  Set Rng3 = Application.Union(Cells(Target.Row, "AF").Resize(, 12), Cells(Target.Row, "AQ"))
                  Rng3.Interior.ColorIndex = 42
                  End If
      End If
  
      If Not Intersect(Target, Range("J7:J400")) Is Nothing Then
          Set Cell = Worksheets("Lists").Range("B2:C23")
          res = Application.VLookup(Target, Cell, 2, False)
      If IsError(res) Then
          Range("K" &amp; Target.Row).Value = ""
      Else
          Range("K" &amp; Target.Row).Value = res
      End If
      End If
  
  End Sub
  </code></pre>
  
  <p>What I'd like to do, if at all possible, is when the date is inserted into column "A", I'd like to insert the text value "Select" on the same row in column "C". This value is taken from the first value I have in a drop down menu, set up on a sheet called "Lists" with the named range "RDStaff".</p>
  
  <p>Could someone perhaps tell me please how I may go about changing the functionality, so that as soon as column "A" is populated with the date, the first value from my list i.e. "Select" is automatically populated in column "C"?</p>
  
  <p>Many thanks and kind regards</p>
  
  <p>Chris</p>