Running Macro when change made to worksheet

[jtay]

The intent of the code below is to run the marco HideCol when a change is made in cell C10. If I actually change the value in C10 it works. However, I have C10 linked to another worksheet (=anotherpage!A13). When the value changes, my macro is not executing.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Set changed = Range("C10")
If Not Intersect(Target, changed) Is Nothing Then
HideCol
End If
End Sub

Any thoughts on how to make this work? Thanks in advance.

 

[Michael M]

Hi jtay and Welcome to the Board
Try
changing

Sub Worksheet_Change(ByVal Target As Range)

TO

Private Sub Worksheet_Calculate()

 

[jtay]

Thanks Michael M!

When I do that, it no longer likes the line:

If Not Intersect(Target, changed) Is Nothing Then

Sorry, I'm new to VBA. I tried taking target out of the line above, but that didn't seem to help?

 

[Michael M]

Ok, my bad, I didn't read the question correctly
You'll need to put a reference cell in the sheet, see my example.
Basically this triggers the change, "Z1" can be anywhere, and will = C10, when the formula changes C10 the event is trigered and the new value transferred to "Z1"

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    If Range("C10") <> Range("Z1") Then
        Range("Z1") = Range("C10")
        HideCol
    End If
Application.EnableEvents = True
End Sub