Run macro on cell value

[smoorcroft]

Is there a way to have a macro run if a certain value is entered into a specific cell. For example if "17580" is entered into g2 then run a macro called Check

Thanks for any help, if it is at all possible

 

[jbeaucaire]

You would need to add a sheet-event macro to the sheet module to accomplish this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range

For Each cell in Intersect(Target, Range("G2"))
    If cell = "17580" Then Call Check
Next cell

End Sub

Right-click the tab name and choose VIEW CODE, paste that macro into the sheet module. It's active all the time.

Did you really mean "just" cell G2, or the whole G column? If it's the whole column, then:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range

For Each cell in Intersect(Target, Range("G:G"))
    If cell = "17580" Then Call Check
Next cell

End Sub

Lastly, if you need to feed in which cell in column G got that value, then:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range

For Each cell in Intersect(Target, Range("G:G"))
    If cell = "17580" Then Call Check(cell)
Next cell

End Sub

...where the macro Check is designed to receive a parameter:

Sub Check(cell as Range)

 

[meldoc]

Hello and welcome to MrExcel

Try this in your worksheet module.

<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>  <SPAN style="color:#00007F">If</SPAN> Target.Address = "$G$2" <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target = 17580 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> test <SPAN style="color:#007F00">'Change to YourMacro name</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[smoorcroft]

Thank you meldoc, that worked exactly as I had hoped