How to make VBA act whenever a given value changes

[RadekW]

Dear VBA Experts,

I've wrote a simple VBA code - the aim is to make a shape UP visible whenever the value next to the shape is above 0, and to make the shape DN visible whenever the value is below 0.

The problem is that if I change the A1 cell value manually - the macro works well and the shape is displayed properly.
But if I use excel formula to set the A1 cell value [=IF(A2>0,1,2)] and then change the A2 value - it does not work. What shall I change?

Please, find my macro below.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Value = 1 Then
        ActiveSheet.Shapes("Up").Visible = True
        ActiveSheet.Shapes("Dn").Visible = False
    Else
            ActiveSheet.Shapes("Up").Visible = False
            ActiveSheet.Shapes("Dn").Visible = True
  End If
  End If
End Sub

 

[vletm]

RadekW
You change cell's A2 value - not cell's A1 value.
Your code checks cell's actions - formula do not trigger Your used code.

 

[S P P]

This is how it works

>>> use code - tags <<<

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A2" Then
    Select Case True
       Case Range("A2") > 0 Or Range("A2") < 0
       Range("A1").Select
End Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then
    Select Case True
       Case Range("A1") = 1
       ActiveSheet.Shapes("Up").Visible = True
       ActiveSheet.Shapes("Dn").Visible = False
       Case Range("A1") <> 1
       ActiveSheet.Shapes("Dn").Visible = True
       ActiveSheet.Shapes("Up").Visible = False
End Select
End If
End Sub

 

[RadekW]

Thank you for your help!!!

 

[S P P]

It's good to be able to help and be helped.

 

[p45cal]

Private Sub Worksheet_Calculate()
Shapes("Up").Visible = Range("A1") = 1
Shapes("Dn").Visible = Range("A1") <> 1
End Sub