VBA for Calling Cell in Row Range and Writing to Same Row but Different Column for Multiple Rows

[mattxd92]

Not sure if the title is fully clear on what im asking for as i am new to using VBA in excel but essentially I am looking to add in a date field to a cell in the same row but in a different column that is consistent. I feel like there is some way of seeing the selected cell in the range and writing to the specific column but im not sure how to go about it.
As an example:

Data Changed

XXXX

XXXX

XXXX

XXXX

XXXX

Date of Change

Data Changed

XXXX

XXXX

XXXX

XXXX

XXXX

Date of Change

This code I found is close to what I need but it requires me to manually enter the cell values which is very time consuming.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1
Then
ActiveSheet.Cells(7, 1).Value = Format(Date, "mm/dd/yyyy")
End If
End Sub

I need a way to make it work like a normal function does where it changes the cells used when you drag it. Is there a wildcard like character or a better way of calling this? I know its a bit hard to understand since I don't know the right terminology but any help would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

Your question is very confusing. Please tell us what you want/expect to happen (in plain English), and be as detailed as possible (i.e. referencing cell addresses).
It may be helpful if you walk us through a detailed example, step-by-step.

 

[mattxd92]

Welcome to the Board!

Your question is very confusing. Please tell us what you want/expect to happen (in plain English), and be as detailed as possible (i.e. referencing cell addresses).
It may be helpful if you walk us through a detailed example, step-by-step.

Sorry!

So essentially there are changes I have to make to a specific column of data a few times a year and need a way to have a date added to another column to make it easier to keep track of when the last time a change to that specific cell was made that stays in the same row.

Step-by-step:
1. Cell A1 is changed
2. Cell J1 gets a date added to it
3. Cell A4 is changed
4. Cell J4 gets a date added to it


Main issue is making it where it will work automatically for every cell in the range and write the date of change to the corresponding row.

I can use this code shown in the original post but need to manually change the bolded values every time for each cell
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1
Then
ActiveSheet.Cells(7, 1).Value = Format(Date, "mm/dd/yyyy")
End If
End Sub

 

[Joe4]

If you want it to update column J any time column A is updated, just modify the code like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0,9) = Format(Date, "mm/dd/yyyy")
    End If
End Sub

This will work automatically for ALL rows, without you have to change anything.

 

[mattxd92]

If you want it to update column J any time column A is updated, just modify the code like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0,9) = Format(Date, "mm/dd/yyyy")
    End If
End Sub

This will work automatically for ALL rows, without you have to change anything.

my god that was simple! the Target.Column was the missing link for me that is super helpful thank you so much!!!

 

[Joe4]

You are welcome.

Note that your original line of code was this:

If Target.Column = 1 And Target.Row = 1 Then

That basically says only run if the column number is 1 (column "A") and the row number is 1.
So it would only work when cell A1 is updated.

By removing the "Target.Row = 1" part, we are now just checking to see if the update is happening in the first column (column "A"). We don't want to limit the rows.

And thenthere is this part:

Target.Offset(0,9)

Note that in these procedures "Target" is the cell was just updated.
Since we want to update column J of the same row where column A was updated, we just move 9 columns to the right of the Target.

Hope that all makes sense!

 

[mattxd92]

You are welcome.

Note that your original line of code was this:

If Target.Column = 1 And Target.Row = 1 Then

That basically says only run if the column number is 1 (column "A") and the row number is 1.
So it would only work when cell A1 is updated.

By removing the "Target.Row = 1" part, we are now just checking to see if the update is happening in the first column (column "A"). We don't want to limit the rows.

And thenthere is this part:

Target.Offset(0,9)

Note that in these procedures "Target" is the cell was just updated.
Since we want to update column J of the same row where column A was updated, we just move 9 columns to the right of the Target.

Hope that all makes sense!

yes that makes far more sense thank you very much!