Selection Change where a merged cell is involved

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

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

I never used Merged Cells that way. Try

Code:

---

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

---

Code:

---

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

---

Quote:

---

Originally Posted by 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

---

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

Thank you for all Info
SamT your vba works thanks
Paul_Hossler in hind site I should have maybe used Worksheet_SelectionChange but not sure how to

Quote:

---

Originally Posted by Marsau

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

---

You did use Worksheet_SelectionChange

I was thinking

Code:

---

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

---

Thank you will try last post