VBA run macro on cell change - how do I make it execute immediately on change?

[Brad B]

The macro is executing correctly, but not at the right time.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim KeyCells As Range


' The variable KeyCells contains the cells that will cause an alert when they are changed.
Set KeyCells = Worksheets("PP Master").Range("F8")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


Worksheets("PP Master").Range("i9").Value = Worksheets("PP Master").Range("f8").Value

End If
End Sub




If I change f8 (via data validation drop down), it doesn't copy the value over until I
1) click away from F8
2) click ON F8.
*Once I click on F8 again (even if I don't change it), the macro runs. I want it to run instantly, the first time I select the new value from the drop down. Suggestions?

 

[tonyyy]

You might try changing...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

Cheers,

tonyyy

 

[L. Howard]

Try removing the text in RED

So you will be using a Change Event macro instead of a SelectionChange Event.

With the Change event when you select from the drop down that will affect a CHANGE to the Target cell.

Howard

Private Sub Worksheet_[COLOR=#FF0000]Selection[/COLOR]Change(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will cause an alert when they are changed.
    Set KeyCells = Worksheets("PP Master").Range("F8")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        Worksheets("PP Master").Range("i9").Value = Worksheets("PP Master").Range("f8").Value

    End If
 End Sub

 

[Brad B]

Thanks! That solved it!

 

[tonyyy]

You're welcome. Glad it worked out...

 

[CatyH]

You might try changing...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

Cheers,

tonyyy

once again tonyyy to the rescue... I'm still in awe of your help from YEARS ago!