Selection Change where a merged cell is involved

[Marsau]

Hi there all
I have a merged cell = P5 to U5

I have written the following vba to call a macro if this cell changes but to no success


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Address = "p5" Then
Call ADVDAY1


End If


End Sub


Pls assist to enable me to learn what I have done wrong

[Marsau]

Got it solved with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:BC17")) Is Nothing Then
Call ADVDAY1
End If
End Sub

Better suggestions would be appreciated

[SamT]

I never used Merged Cells that way. Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.MergeArea.Cells(1).Address = "$P$5" Then ADVDAY1 '$ signs required, Caps required
If Target.MergeArea.Cells(1).Address = "$W$5" Then ADVDAY2
End Sub


Sub Test()
MsgBox Range("a1").Address & ": test1"
MsgBox Cells(1, 1).Address & ": test2"
MsgBox Cells(1).Address & ": test3"
End Sub

[Paul_Hossler]

Hi there all I have a merged cell = P5 to U5 I have written the following vba to call a macro if this cell changes but to no success Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "p5" Then Call ADVDAY1 End If End Sub Pls assist to enable me to learn what I have done wrong


1. Do you mean Worksheet_SelectionChange or Worksheet_Change?


The first fires when you just click any cell, the second when you change any cell




2. I think a little investigation on your part would find that the .Address is returned as "$P$5" so the test will always be False

[Marsau]

Thank you for all Info
SamT (http://www.vbaexpress.com/forum/member.php?6494-SamT) your vba works thanks
Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler) in hind site I should have maybe used Worksheet_SelectionChange but not sure how to

[Paul_Hossler]

in hind site I should have maybe used Worksheet_SelectionChange but not sure how to


You did use Worksheet_SelectionChange

I was thinking



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$P$5" Then
Application.EnableEvents = False
Call ADVDAY1
Application.EnableEvents = True
End If

End Sub

[Marsau]

Thank you will try last post