Private Sub Target Question

TGwilding

Board Regular
Joined
Jun 13, 2005
Messages
65
I am working with the following code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub


If Intersect(Target, Range("u11:u32")) Is Nothing Then Exit Sub

Application.EnableEvents = False


If Target <> "" Then Range("t69").Select
Application.CutCopyMode = False
Selection.Copy
Range("s69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("u9").Select
Application.EnableEvents = True


If Target.Cells.Count > 1 Then Exit Sub


If Intersect(Target, Range("y11:y32")) Is Nothing Then Exit Sub

Application.EnableEvents = False


If Target <> "" Then Range("x69").Select
Application.CutCopyMode = False
Selection.Copy
Range("w69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("y9").Select
Application.EnableEvents = True

End Sub

The first part is working perfectly, but when I change values in range y11:y32, nothing happens.

Can anyone help on this?
thanks
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
This line

If Intersect(Target, Range("u11:u32")) Is Nothing Then Exit Sub

ells the code to ignore any change not in U11:U32

If you want to monitor a different range, change the reference to that range reference.

EDIT - AActually, try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("u11:u32")) Is Nothing Then
        If Target <> "" Then Range("s69").Value = Range("t69").Value
        Range("u9").Select
    ElseIf Not Intersect(Target, Range("y11:y32")) Is Nothing Then
        If Target <> "" Then Range("s69").Value = Range("x69").Value
        Range("y9").Select
    End If
    Application.EnableEvents = True
End Sub
 

maytas

New Member
Joined
Sep 5, 2006
Messages
18
Hi.

Replace
Code:
If Intersect(Target, Range("u11:u32")) Is Nothing Then Exit Sub
with

Code:
If Not Intersect(Target, Range("u11:u32")) Is Nothing Then
 '*****************************************************

So Replace
Code:
 If Intersect(Target, Range("y11:y32")) Is Nothing Then Exit Sub
with

Code:
 If Not Intersect(Target, Range("y11:y32")) Is Nothing Then 
'**********************************************
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How's this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("U11:U32, Y11:Y32")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column
                <SPAN style="color:#00007F">Case</SPAN> 21
                    <SPAN style="color:#00007F">If</SPAN> Target <> "" <SPAN style="color:#00007F">Then</SPAN> Range("S69").Value = Range("T69").Value
                <SPAN style="color:#00007F">Case</SPAN> 25
                    <SPAN style="color:#00007F">If</SPAN> Target <> "" <SPAN style="color:#00007F">Then</SPAN> Range("W69").Value = Range("X69").Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 

maytas

New Member
Joined
Sep 5, 2006
Messages
18
Hi,
Full Code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Stop
    If Not Intersect(Target, Range("u11:u32")) Is Nothing Then
        If Target <> "" Then Range("t69").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("s69").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("u9").Select
        Application.EnableEvents = True
    ElseIf Intersect(Target, Range("y11:y32")) Is Nothing Then Exit Sub
        If Target <> "" Then Range("x69").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("w69").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("y9").Select
    End If
    Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,111,911
Messages
5,541,543
Members
410,549
Latest member
Anaarchie
Top