Intersect needs fixing

[Vacuum Sealed]

Hi all

Is there another way to do the following so that it fires after the cell
updates, be it via the dropdown or manually entered please....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B2:B100")) Is Nothing Then

With ActiveCell
.Offset(0, 2).Value = "Y"
End With

End If

End Sub

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B2:B100")) Is Nothing Then

With Target
.Offset(0, 2).Value = "Y"
End With

End If
End Sub


Gord Dibben MS Excel MVP

 

[Vacuum Sealed]

Thx Gord.

Sheet magic would be so much tougher without you guy's...

Cheers

 

[Vacuum Sealed]

Still have probs Gord

When I click along the intersect column, it automagically inserts into
target cell without waiting for any change in said column...

What should I be using so that it is similar to AccessDB's AfterUpdate then
do something....

TIA
Mick.

 

[GS]

Vacuum Sealed formulated the question :

Still have probs Gord

When I click along the intersect column, it automagically inserts into target
cell without waiting for any change in said column...

What should I be using so that it is similar to AccessDB's AfterUpdate then
do something....

TIA
Mick.

This suggests the code is still in the SelectionChange event, which is
not where Gord's solution is. The SelectionChange event fires whenever
you move to another cell. The Change event (suggested by Gord) only
fires after cells are updated, whether directly via in cell editing or
by formula result.

 

[Vacuum Sealed]

Yep..

Thx for pointing out to all that I'm not only thick, but I also need coke
bottle glasses to see the forest through the trees..........

Appreciate the pointer..

Cheers
Mick..

And thx again Gord, apologies for the doubt....

 

[Gord Dibben]

Thanks for jumping in GS

Change events are not generally used when dealing with formulas.

Calculate event is more common in that case.

Having said that, here is some code from Rick Rothstein if you wanted to use
Precedents with change event.

Private Sub Worksheet_Change(ByVal Target As Range)
'when A1 has a formula.....in place of worksheet_calculate
'Rick Rothstein July 11, 2008
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1").Precedents) Is Nothing Then
Application.EnableEvents = False
With Range("C1")
.ClearComments
.AddComment
.Comment.text Range("A1").Value
End With
Application.EnableEvents = True
End If
End Sub


Gord

 

[Vacuum Sealed]

Thx again Gord, also for pointing out Rick's code.

Cheers
Mick