Cell Change Macro

[ManUBlueJay]

Is there a way to have the worksheets change method monitor 2 cells and depending which one changes run code. Here is what I did but cannot seem to have the code run past the first if.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A7") = "B1"
Else
If Not Intersect(Target, Range("B2")) Is Nothing Then
Range("A7") = "B2"

End If
End If

End Sub

 

[James006]

Hello,

You could test following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Or Target.Address = "$B$2" Then Range("A7") = Target
End Sub

Hope this will help

 

[ManUBlueJay]

Thank you, but it does not seem to work. I stepped it through and it does not get to the second line

 

[James006]

Hi again,

What do you call ' the second line ' ...?

May be misunderstood your objective ...

 

[ManUBlueJay]

The second line of your code.
It skips out after the first line

 

[Joe4]

That seems to imply that it is seeing multiple cells being updated at once, so it is exiting the code, as per this line:

If Target.Count > 1 Then Exit Sub

How exactly is the data being updated?
Are they the result of formulas?
Are you copy/pasting multiple cells at once?
Or do you have merged cells that you are updating?

It maybe helpful to walk us through an actual example of exactly what you are doing.

 

[ManUBlueJay]

Sorry I was trying to keep it simple.
B1 and B2 are part of a pivot table controlled by 2 seperate slicers.
If B1 changes I want a given code to run, If B2 changes I want a different code to run.

 

[Joe4]

If they are calculated values, you cannot use Worksheet_Change on it. That event procedure runs when values in specified cells are manually updated.
If the values change due to calculations, that would be a Worksheet_Calculate event procedure. The tricky part about using that it runs whenever ANY cell on the sheet is re-calculated. It cannot tell which cell was re-calculated.

 

[ManUBlueJay]

How about code using the PivotTableChangeSync. I tried but didn't mnanage

 

[Joe4]

How about code using the PivotTableChangeSync. I tried but didn't mnanage

I am afraid I probably cannot help you with that. I very seldom work with Pivot Tables (just not something I have to work with in my line of work).
Most stuff that would fall into that category we end up using database programs for instead of Excel.

Had I known that Pivot Table/Slicers were involved, I probably would have not put my two cents in.