How can I reference a cell that was just edited in VBA?

[Boopin]

Hi, so far with this code I can only reference the active cell that is clicked on AFTER a cell is edited; I've tried to work around this by having the users only click ENTER after editing a cell, and by offsetting everything by -1 row. I would like to directly reference the cell that was edited without having to do this. Here are my codes (two separate macros):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Check As Range

Set Check = ActiveCell.Offset(-1, 7)

' if activecell is in M column, macro is run
If Not Intersect(Target, Range("M:M")) Is Nothing Then

Call ChangeSpreading

End If

End Sub


Sub ChangeSpreading()

Dim Q1 As Range
Dim Q2 As Range
Dim Q3 As Range
Dim Q4 As Range
Dim Total As Range

Set Q1 = ActiveCell.Offset(-1, 2)
Set Q2 = ActiveCell.Offset(-1, 3)
Set Q3 = ActiveCell.Offset(-1, 4)
Set Q4 = ActiveCell.Offset(-1, 5)
Set Total = ActiveCell.Offset(-1, -1)
Set Spreadmodel = ActiveCell.Offset(-1, 0)

'Coding for Q spreading amounts based on M column
If Spreadmodel = 1 Then

Q1 = Total
Q2 = 0
Q3 = 0
Q4 = 0

ElseIf Spreadmodel = 2 Then

Q1 = 0
Q2 = Total
Q3 = 0
Q4 = 0

etc...

End If
End Sub

Basically, if they enter 2 into a cell in column M for example, i would like it to take the data from Total and correctly spread it to Quarter 2 (Q2), both from the same row (no matter where they click after).

Help is greatly appreciated!

 

[Marcelo Branco]

Hi and welcome to Mr Excel

Target is exactly the cell (or cells) that was just edited.

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        MsgBox Target.Address
        'more code
        'more code
    End If
End Sub

Hope this helps

M.