Cell value change runs a macro?

[Steach91]

Is there a way to make it so that if a cell value changes, a macro is
called?

 

[Nick Hodge]

Sure

You use a Worksheet_Change() event. To access this press alt+f11 to access
the VBE, right click on the worksheet concerned and select view code... In
the left drop-down in the resulting window select worksheet and in the right
one select Change. You will get a template as below. Now the rub... you
have to be able to write VBA code to do anything with it. (Obviously you
could record some and cut and paste it between the Private Sub...End Sub
lines, but eventually you will need to hand write some for sure)

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[L. Howard Kittle]

From Worksheet Object Events help.
Example
This example changes the color of changed cells to blue.

Private Sub Worksheet_Change(ByVal Target as Range)
Target.Font.ColorIndex = 5
End SubHTH
Regards,
Howard

 

[Steach91]

Thanks for the prompt replies... As seems to always be the case, I
probably made a broad question too broad..

I'm looking for a way to run a macro if cell (L16)'s value changes. Not
the whole worksheet.

Again, thanks for the help!

 

[Arvi Laanemets]

Hi

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 And Target.Row = 16 Then
' Add your code here
MsgBox "There was a change in cell L16"
End If
End Sub



Arvi Laanemets

 

[Nick Hodge]

Worksheet_Change monitors all cells, you just need to tell it which one,
like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("L16")) Is Nothing Then
OtherMacro
End If
End Sub

This will run a macro called OtherMacro if cell L16 is changed.

Now you need to beware if the macro makes other changes to the worksheet or
else you code will be called each time it is. You can prevent this by
adding

Application.EnableEvents=False

but remember to turn them back on at any place where code can exit
(including errors) or you will be left with no events at all in Excel

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Steach91]

Thank you very much for the help