Marty,
I'm not following the part about AAA, BBB..... but this should get you
started:


In the ThisWorkbook module:

Private Sub Worksheet_Change(ByVal Target as Range)

' Eliminate cases of changing more than one cell at a time
If Target.Count1 then Exit Sub

'Make sure the change is to any cell in MyRange: E7:H31
If Not Intersect(Target,Range("E7:H31") Is Nothing Then
'Prevent this event from triggering itself over and over in a loop
Application.EnableEvents=False

Call YourMacro 'which is in a Standard Module
here is where the Range("J2")=DIST variable
comes in
End If

'Turn Events back on--this is not automatic like screen updating is
Application.EnableEvents=True
End Sub


"Marty" wrote:

Hello:

I'm using Excel 2003. I'm wanting to execute a series of code steps upon a
change to any cell within a specified range of cells. From some searching, I
found the following code example, which does what it says it's supposed to do:
================================================== ===
'This procedure will change the value of F10 to TRUE if the value in A1 is
changed
'to a value greater than or equal to 10. Note that we change
'Application.EnableEvents to False in order to prevent the Change event from
'calling itself, and then restore the setting back to True.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value = 10 Then
Range("F10").Value = True
Else
Range("F10").Value = False
End If
Application.EnableEvents = True
End If
End Sub
================================================== ===
Seems simple enough. So I reasoned that I could delete the If-Else-End-If
block and replace it with my code that I want to execute, and change the
range from $A1$1 to the range of cells on which I want to activate my code if
it changes. In my case, this is "E7:H31"

I can't get it to work. Basically, I want to put the new value of DIST into
cell J2 if any of AAA, BBB or CCC changes.

Here is A SMALL PORTION of the code (NOTE: BBB, CCC and DDD are within the
range of cells that I want to monitor and execute upon changes. AAA and MDC
are only one of four fixed values and are outside the range. MDC is a
constant, also outside the range):
================================================== ===
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "E7:H31" Then
Application.EnableEvents = False
Dim MYSHEET as Object
Set MYSHEET = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")

If AAA = 0 And BBB = "M" And CCC = "3D" And DDD = "M" Then
DIST = {formula which contains AAA, BBB, CCC and DDD as variables}
If DIST < 0 Then
DIST = 0
End If
If DIST MDC Then
DIST = MDC
End If
MYSHEET.Cells(2, 10) = DIST
End If

Application.EnableEvents = True
End If
End Sub
================================================== ==
I get no errors, but I also get no results in J2 (I know the IF AAA = 0...
conditions are satisfied). Can anyone see what I'm doing wrong?

Thanks in advance.

MARTY