Automatic Macro Execution Upon Cell Values Changing

[Guest]

I need to have a method to have a macro automatically run whenever a specific
range of cells change. I am not a great VBA programmer so hopefully this is
easy to do.

 

[Stephen Bullen]

Hi Brian,

I need to have a method to have a macro automatically run whenever a specific
range of cells change. I am not a great VBA programmer so hopefully this is
easy to do.

Sure. Go to the VBE, find the code module for the sheet, select 'Worksheet' in
the top-left dropdown of the code window and the Change event in the top-right.
Then put some code in that event. In the code below, I've assumed you've named
the range to check 'rngChanges':

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCells As Range

'Get the cells that have changed within our range
Set rngCells = Application.Intersect(Target, Me.Range("rngChanges"))

If Not rngCells Is Nothing Then
'rngCells points to the cell(s) that have changed within the range
rngCells.Interior.ColorIndex = 3
End If

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[wardides]

Brian,

I just want the code in the change event to run if a certain cell (e.
C5) is changed.

how do I tell the worksheet_change event that it should only run if c
is changed

 

[Stephen Bullen]

Hi Wardides,

how do I tell the worksheet_change event that it should only run if c5
is changed ?

It will always run - you have to test the Target parameter to see if
it's the one you want - such by checking its address, or seeing if it's
inside a named range.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk