title: How can I get a cell within a range to hardcode once the cell changes?
body: <p>I need to get a cell within a range to hardcode when a status is selected in another column (same row) on the worksheet.  The purpose is so I can measure how long a new hire candidate sits in each step (or status) of the hiring process.</p>
  
  <p>I've tried writing code to get the macro to start once the workbook opens (module1), and then to loop through the specified range of cells (AC3:AQ5000) once the status in column J is updated using a worksheet_change event (sheet1).  Columns AC through AQ have a formula to date the cell once column J is updated to a status that matches each column <code>(=IF($J5=AC$1,IF(AC5&lt;&gt;"",AC5,TODAY()),""))</code>.  Circular reference has been turned off.</p>
  
  <pre><code>Private Sub Worksheet_change(ByVal Target As Range)
  
      Dim Target As Range
      Dim MyCell As Range
      Set Target = Sheet1.Range("ac3:aq5000")
      For Each MyCell In Target
          If MyCell.Value &gt; "" Then
              MyCell.Copy
              MyCell.PasteSpecial Paste:=x1pasteformats
          End If
      Next MyCell
  
  End Sub
  </code></pre>
  
  <p>I expect to get each column to hardcode the date once the cell changes from  blank to (today's) date.  So far I receive errors for "ambiguous name detected worksheet_change", "compile error expected identifier", or nothing happens.</p>