Change this line:
Set myRngToInspect = Me.Range("M:M,X:X")
to include just the columns that you want to monitor for changes.

Dan wrote:

should say, col M has no affect to D (once updated: semi-static, e.g. cols:
CO:CS base work data cols), if record up to/ since date then macro1?: jump
to next work, else macro2 jump to base work data input.

"Dave Peterson" wrote:

So if you're making changes in column M or X (say) that make the formula in
column D recalculate, you could use something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("M:M,X:X")

With Target
If .Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(.Cells, myRngToInspect) Is Nothing Then
Exit Sub 'not in M or X
End If

Select Case Target.EntireRow.Range("D1").Value
Case Is = 2: Call Macro2
Case Is = 3: Call Macro3
Case Else
'do nothing or call a different macro
End Select
End With
End Sub

This kind of thing really depends on how that formula in column D is written.
If it depends on other cells besides the ones in column M and X in the same row,
then worksheet_change may not be the best way to go.

But if you have that complex formula, then you may have to check all the cells
in the column D to know what to do????


Dan wrote:

hi, sorry for delay, had spent couple 8 hour days trying to do this item.
know it is relatively simple, just takes me much more time since don't know
macro's/ vba, may be why i don't always explain well. in trying to keep to
just basics (will give some info..)

have sample of date macro WITH ME certain col enter date in different col.
so other: col D has formula's, when I do data entry (WITH ME.. same?) on
eg col M, if col D equals a certain value, =2, then do MACRO 1, else do
MACRO 2

i've seen IF, ELSE, END IF. have been collecting 1 or 2 other examples but
haven't had time to excessive test simple task of IF in column D, to choose
next macro to occur.

my experience with macro's is minimal, sorry if some what i say misleads but
to do an eg: set isect.. with eclipse after will not help me since i do
not know what the "before the etcetera" was to start with.. maybe isect is a
"anything" name is a given nickname (but worse for me i do not know, if is
the case: how the 'intersect' is working)

same thing different way?: in any row: 500, if col/ cell D500 = 2, do x,
else y
(I can figure out the: else, x, y) thanks

"Dave Peterson" wrote:

I don't understand.

Column D is a complete column...not a single cell with a single value.

And you haven't explained how M4 is related to column D.



Dan wrote:

i guesse had that already posted, that item done.. if that is not the
problem,
then I need to test for M4 equals 2? are we using isect for what had been
defined?

then might be I need to use your example (copy below), but don't see it
address how to test M4 range if equals 2 ?
back to simple

If column D = 2, perform macro1, else perform macro2

With Target
If Intersect(.Cells, M4) Is Nothing Then
'not in M4
Else
'in M4
End If
End With

"Dave Peterson" wrote:

isect is an object. You need to use the Set statement.

Set isect = ...

Dan wrote:

hi, sorry small window- missed pasting items at bottom. have reviewed your
suggestions some as well as could see. even with my small corrections -
straightening things out (to & from), still getting 1 same error as before.
I am not sure what am doing wrong.

'goal: i want to hit enter in col CN/J6, if review 'on', if col D/M4 =2,
jump cursor to col DC/K7, else col CW/K2.
'col D/ designated by m4 is formula column calcs if different date col is
specific date, e.g.: IF(CC9$S$3,2,0)
'merely if formula col D = 2, then macro A, else macro B
'representation of columns are in cells: J6 M4 K7, as listed at bottom.

' Dim J6 As Range 'installed above
' Set J6 = Range("J6")
' Dim K7 As Range
' Set K7 = Range("K7")
' Dim M4 As Range
' Set M4 = Me.Range("M4")

Dim iSect As Range
' 'Set iSect = Intersect(Target, M4) 'may have had J6 here before
Set iSect = Application.Intersect(Target, M4)

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then
If Range(M6).Value "0" Then 'single cell REVIEW ON

'If Range(M4).Value = "2" Then 'orig attempt, column D/M4,
Problem: ck intersect/isect?, old Error: type mismatch, trying to add: If
date col D9=2/3/4?

'Error: Object or With block variable not set
iSect = Application.Intersect(Target, M4)
'old: iSect = Application.Intersect(Range(Target.Address), M4)
'old Error: Object or With block variable not set
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then 'if value in col D/M4 is true

With Me.Cells(.Row, K3).Select 'then to this, if still
true-Error: type mismatch
End With
Else
With Me.Cells(.Row, K7).Select 'else this
End With 'JUMP
End If

Else
With Me.Cells(.Row, K3).Select 'Error: type mismatch
End With
End If
End If

''J6 has CN:CN, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CN6),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$CN6),"$",""),ROW(),"")

''M4 has D:D, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$D4),"$","") ,ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address ",$D4),"$",""),ROW(),"")

''K7 has DC:DC, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$DC7),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DC7),"$",""),ROW(),"")

"Dave Peterson" wrote:

I have no idea how M4 and column D are related. Or how column CW and K3 are.

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson