How 2 assign a macro when a cell changes words

[Alpacino]

How do u assign a macro when a value in a cell changes. I would cell A1 to run macro 2 when cell A1 = Green

Thanks

 

[AlphaFrog]

Is there a formula in cell A1 or a drop-down list or does the user type in "Green" in cell A1? How does cell A1 get changed. It would matter in this case.

If cell A1 has a Drop-down list or you type in green, then this method would work.

Right-click on the sheet tab and select View Code from the pop-up menu. Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If LCase(Target.Value) = "green" Then macro2
    End If
End Sub

If Cell A1 has a formula, then what is the formula?

 

[repairman615]

Maybe this could suit:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("A1"))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> UCase(Target.Value) = "GREEN" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> Macro2<br>    <SPAN style="color:#00007F">Else</SPAN><br>    <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Alpacino]

Thanks for reply. A1 is a result from an if formula. =If(D1>60,"Green","Red") does that help??

 

[AlphaFrog]

Thanks for reply. A1 is a result from an if formula. =If(D1>60,"Green","Red") does that help??

If the user types in a value in D1, then try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "[COLOR="Red"]$D$1[/COLOR]" Then
        If LCase(Range("[COLOR="Red"]A1[/COLOR]").Value) = "green" Then macro2
    End If
End Sub

This would trigger when D1 is changed by the user but test the result for "Green" in A1.

 

[Alpacino]

Thanks I'll Hav a go.

 

[AlphaFrog]

Alternatively, you could put the A1 formula in the code and test if D1 > 60

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "[COLOR="Red"]$D$1[/COLOR]" Then
        If [COLOR="Red"]Target.Value > 60[/COLOR] Then Macro2
    End If
End Sub