Macro to run when a particular cell is updated

AdamS24 · #1 (**permalink**) 03-13-07, 10:47

Hi,

I have been looking for a way to have a macro run when a particular cell is updated. (example: after a cell has been typed in and [enter] pressed it now automatically shifts focus to another work sheet.)

I have looked through the list of events that excel can run macros off, but cant find one that corresponds to the update of one particular cell.

The closest event I have found is for a update to a worksheet:
(Specific code is run for a change to cell C4 in the code below)

Private Sub Worksheet_Change(ByVal Target as Range)
If ActiveCell.Address = "$C$4" Then
MsgBox (ActiveCell.Address & "Is active")
End If
End Sub

(Only problem with this event is that it might make the spreadsheet slower because it runs for all updates/changes made)

Thanks for any help.

shades · #2 (**permalink**) 03-13-07, 13:38

Howdy. You have chosen the correct event. Just include an intersection between Target and your specific cell. For example, assume your cell is P2, then you could use something like this.

Code:

    Dim rng As Range
    ' Target is a range::therefore,it can be more than one cell
    Set rng = Intersect(Target, Range("P2"))

Internet Services

Web Development

Digital Marketing

Consumer Tech