Macro Selects Wrong Row - Excel 2010

[ggelineau]

I have the following macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Or Target.Column = 2 Then
MsgBox "You just changed " & Target.Address
MsgBox "You just changed Row " & Target.Row
MsgBox "I would like to change the date in Cell " & Target.Range("C" & Target.Row).Address
Target.Range("C" & Target.Row).Value = Date

End If
End Sub

The intended purpose of this macro is to enter today's date into column "C" when a change is made to a cell in Column "A" or "B". I try to use the Row of the cell that is changed in Column "A" or "B" in order to designate what cell I would like to change in Column "C" (i.e. if I change A2 or B2, Cell C2 should have today's date). However, the above macro is placing the date in the wrong cell and column depending on what cell I choose. For instance, if I make changes to cell A2, the date in C3 is changed (the date in C2 should be changed). And when I make changes to cell B2, the date in D3 is changed (the date in C2 should be changed). Oddly enough, when I make changes to cell A1 (title row), it works (i.e. Cell C1 is changed to show today's date), but I would rather it did not take into account changes to the title row (row 1).

Any assistance is much appreciated.

Thanks,
Godfrey

 

[RoryA]

You need to remove the reference to Target in the output ranges or they become relative to the changed row:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Or Target.Column = 2 Then
MsgBox "You just changed " & Target.Address
MsgBox "You just changed Row " & Target.Row
MsgBox "I would like to change the date in Cell " & Range("C" & Target.Row).Address
Range("C" & Target.Row).Value = Date

End If
End Sub

 

[Greg Truby]

Please use CODE tags when posting code. You want to toggle the ENABLEEVENTS property whenever you change cell values inside a CHANGE event handler. Otherwise you risk creating an infinite loop.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or Target.Column = 2 Then
    Application.EnableEvents = False
    Range("C" & Target.Row).Value = Date
    Application.EnableEvents = True
End If
End Sub

 

[ggelineau]

rorya,

Thank you for your prompt reply. How would I go about removing reference to "Target"? Don't I need to make reference to this in order to obtain the proper row number?

Thanks,
Godfrey

 

[RoryA]

See the code I posted. You only need to remove it from in front of the Range calls.
If your changed cell is A1, then you are effectively saying:
Range("A1").Range("B1")
ie. the cell in the first row and second column of A1 (B1 as it happens). So it works for row 1. But if we move to row 2, then its:
Range("A2").Range("B2")
which is the second column and second row of A2 (Excel implicitly expands your range) which is actually B3. And it keeps going the further down you go getting further off each time so in row 3 you get B5, in row 4 you get B7 and so on.

 

[ggelineau]

rorya,

Sorry, I did not notice the change in the code you posted. Your assistance is much appreciated!

Thanks,
Godfrey

 

[Greg Truby]

Godfrey,

I do hope that you saw and noted what I said about the risk of creating an infinite loop when changing cell values in a Change event handler. In this case you only create a recursed stack two-deep. But it's amazingly easy to later go back and make a small change that results in an infinite loop if you don't take care to toggle events.

 

[ggelineau]

Greg,

Thank you for pointing out this potential issue. I have modified my code accordingly.

Thanks,
Godfrey