How can I get a cell within a range to hardcode once the cell changes?

Question

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.

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 (=IF($J5=AC$1,IF(AC5<>"",AC5,TODAY()),"")). Circular reference has been turned off.

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 > "" Then
            MyCell.Copy
            MyCell.PasteSpecial Paste:=x1pasteformats
        End If
    Next MyCell

End Sub

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.

Ahmed AU · Answer 1 · 2019-06-24T04:05:53.167

Failed to clearly understand the objective and following are assumed

Status will be updated in Column J only
If updated status match with Headers (Containing status text) of Column AC to AQ then the corresponding column and Row corresponding to Updated cell is to replaced with current date. If I assumed it correctly then no need to loop through all the cells in Range("ac3:aq5000") instead loop all the headers (row 1) of Column AC to AQ only. There is also no need to operate any macro in module 1.

If the assumed output will be like below

Then the code would be simply:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, cel As Range, col As Long
Set Rng = Intersect(Range("J:J"), Target)
    If Not Rng Is Nothing Then
    Application.EnableEvents = False
        For Each cel In Rng
          For col = 29 To 43
            If cel.Value <> "" And cel.Value = Cells(1, col).Value Then
            Cells(cel.Row, col).Value = Now()
            End If
          Next
        Next cel
    Application.EnableEvents = True   
    End If
End Sub

Thank you. You assumed correctly. I pasted your code into my macro and received a "Variable not defined" error. I added 'col as Range' and then received a "Type mismatch" error. With both errors 'col' in the row 'For col = 29 to 43' was highlighted. — PamelaG, Jun 24 '19 at 01:45
Sorry, Edited the code . In excel VBA any variable representing row or columns number must be declared as Long. Happy you are using `Option Explicit`, it is a good practice and ensure every variable is declared. I skipped that declaration. — Ahmed AU, Jun 24 '19 at 04:11

score -1 · Answer 2 · answered Jun 23 '19 at 02:40

Please try with following, and change the as per your need

Sub pastespecial()
Dim rangevalue, cellvalue As Range
Set rangevalue = Sheet1.Range("a1:a2")
For Each cellvalue In rangevalue
    If cellvalue > "" Then
        cellvalue.Copy
        cellvalue.pastespecial xlPasteValues
    End If
Next cellvalue


End Sub

How can I get a cell within a range to hardcode once the cell changes?

2 Answers2