• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6077
  • Last Modified:

Excel VBA Run macro when cell value changes from a formula

I have a data validation list in cell B3.  I want to run a macro when this value changes.  I have a formula in cell P2 that is =B3, so whatever the value in B3 is what shows in P2.  I have the below vba code, hoping my clearA macro will run, but it is not working.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Target, Range("P2")) Is Nothing Then
If Target.Address = "$P$2" Then
clearA
End If
End Sub

Open in new window

0
jnikodym
Asked:
jnikodym
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$3" Then
clearA
End If
End Sub
0
 
Shanan212Commented:
Try this

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
MsgBox "Hi"
End If

End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
Instead of checking if P2 has changed check if B3 has changed.
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("B3")) Is Nothing Then

      clearA
   End If 

End Sub 

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Does that mean that the first one does not work?
0
 
jnikodymAuthor Commented:
the Worksheet_SelectionChange part of your solution was causing an issue.  When i changed it to Worksheet_Change it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now