I'm not sure if this'll work for you, but I like this kind of thing:

if target.cells.count 1 then exit sub
if intersect(target,me.range("e7:h31")) is nothing then exit sub

(one cell at a time and within e7:h31)



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

--

Dave Peterson