Worksheet Change Event

[DBaker7777]

I am trying to combine the following different intersects. Any help is appreciated :

private sub worksheet_change(byval target as range)

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If intersect (target, range (“b3:b23”) is nothing then exit sub
Application.enableevents=false
Set fnd = Sheets(“sheet2”).range (“a2:a25”).find(target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
End if
Dim Bnd as Range
If target.countlarge > 1 the. Exit sub
If intersect (target.range. (“C3:c23”) is nothing the. Exit sub
Application.enableevents=false
Set bnd =Sheets(“sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset),1).value = “No”
End if
Application.EnableEvents = True
End sub

 

[Anthony47]

May be this following approach:

If target.CountLarge > 1 Then Exit Sub
    If Not Intersect(target, Range("b3:b23")) Is Nothing Then
        'what to do witd the first range
        
    ElseIf Not Intersect(target, Range("C3:C23")) Is Nothing Then
        'what to do with the second range
    
    End If

End If

Try...

 

[DBaker7777]

Thank you for the response. Nothing happened when I entered data into a cell in column b or c.

 

[Anthony47]

Could you share the code you used?

 

[Joe4]

Note that you have to be real careful when you use code like:

Application.EnableEvents=False

It is important to understand what this does. It disables event procedure code (like "Worksheet_Change") from running, so you do not get caught in an infinite loop.
The logic is if you have code that runs when values are changing, and your code itself changes values, then you need to temporarily disable events so the changes made by the code do not call the code again, and you get caught in a loop.

So quite often, you temporarily disable events with that line of code, make the changes, and then re-enable it with:

Application.EnableEvents=True

However, what sometimes happens is if your code runs and disables the code, and either it errors out, or exits before it turns them back on, this automated event procedure code won't run anymore in that Excel session until you turn events back on.

You can do that by adding the procedure and running it manually:

Sub ReEnableEvents()
    Application.EnableEvents=True
End Sub

 

[DBaker7777]

Could you share the code you used?

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If not intersect (target, range (“b3:b23”) is nothing then
Application.enableevents=false
Set fnd = Sheets(“sheet2”).range (“a2:a25”).find(target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
elseIf not intersect (target.range. (“C3:c23”) is nothing then
Application.enableevents=false
Set bnd =Sheets(“sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset),1).value = “No”
[/QUOTE]

[QUOTE="Anthony47, post: 5754346, member: 60147"]

Could you share the code you used?

Private Sub Worksheet_Change(byval target as range)

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If Not Intersect (Target, Range (“B3:B23”) is nothing then
Application.enableevents=false
Set Fnd = Sheets(“Sheet2”).range (“A2:A25”).Find(Target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
ElseIf Not Intersect (Target.Range. (“C3:C23”) is nothing then
Dim Bnd as Range
Set bnd = Sheets(“Sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset (,1).value = “No”
End If
End If
Application.EnableEvents = True
End sub

 

[Joe4]

Nothing happened when I entered data into a cell in column b or c.

Did you heed the advice in my last post, run that short macro, and try again?

 

[DBaker7777]

Private Sub Worksheet_Change(byval target as range)

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If Not Intersect (Target, Range (“B3:B23”) is nothing then
Application.enableevents=false
Set Fnd = Sheets(“Sheet2”).range (“A2:A25”).Find(Target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
ElseIf Not Intersect (Target.Range. (“C3:C23”) is nothing then
Dim Bnd as Range
Set bnd = Sheets(“Sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset (,1).value = “No”
End If
End If
Application.EnableEvents = True
End sub

Nevermind, I got it to work with the above. sorry for the trouble and thank you for the help!

 

[Anthony47]

I got it to work with the above. sorry for the trouble and thank you for the help!

Thank you for the feedback.

Don't ignore Joe's warning: when you seem that an event is not triggered that run his small macro:

Sub ReEnableEvents()
Application.EnableEvents=True
End Sub

In case excel has been left with the "event detection" disabled that macro will reenable it (without the need for restarting Excel)

Bye

 

[DBaker7777]

Thank you for the feedback.

Don't ignore Joe's warning: when you seem that an event is not triggered that run his small macro:

Sub ReEnableEvents()
Application.EnableEvents=True
End Sub

In case excel has been left with the "event detection" disabled that macro will reenable it (without the need for restarting Excel)

Bye

I did add Joe’s code. Appreciate both of you. Thank you