Carry out action when cells in Range have changed

Hi Cardano, this is a fairly common issue and has to do with how you select the place that you want to put the value.

You could solve this a few different ways, but the simplest is to replace the the BackOne() macro with this:

Sub BackOne()
' BackOne Macro

Sheets("Form One").Range("L9").Value = "BACK"

End Sub

You do not need to select a worksheet or a cell before you input a value into it.

If you wanted to show the worksheet still, you could put this line at the end of the BackOne() macro:

Sheets("Form One").Activate

To learn more about this I suggest checking out the TeachExcel YouTube channel and wait for the next two tutorials; they will go into more detail about how to avoid selecting worksheets and cells when workin with them.

(also, please put CODE tags around your code - i fixed it for you this time)

Let me know how this works!)

Update

To make it work when a formula changes you can use the Calculate event instead of the worksheet change event.

Private Sub Worksheet_Calculate()

'Call Sub BackOne when cell AS10 is greater than zero.
If Range("AS10").Value > 0 Then

    Call BackOne

End If

End Sub

However, note that this will run EVERY time something in that worksheet changes and not just when that specific cell was changed.

You can put as many IF statements as you want to manage all the potential conditions, or use a SWITCH statement, though IF is perfectly fine and easier to grasp when you are kind of new to Excel.

Also, if you don't want it to run each time a formula in the entire worksheet changes, let me know and we can work on a solution to that; however, it seems like it won't be an issue given what you're trying to do and that's why I didn't include a solution for that here.

Hi Cardano.

I have tried this worksheet_change function and it does not recognise the change in the  results of an if function as being a worksheet change - Don can probably confirm that.

Steve