Excel triggered macro

Question

I am struggling to carry out the following. Any help would be greatly appreciated.

The marco only relates to one excel worksheet.

I have 50 rows of information, 11 columns wide (A - K) in each row of column A there sits an IF condition If the IF condition flags the word 'OK' I would like a macro to trigger, and carry out the following to copy the information in cells B to K, and paste special it back into those cells.

And then move onto the next row

The 50 rows of information will fill up over the course of a day.

Each ROW condition in column A is executed only ONCE, starting with ROW 1 and working it's way down to ROW 50

The trigger event 'OK' will come, it is just a matter of waiting for it to happen.

As I say any help would be greatly appreciated

Answer 1

Use the Change event of the sheet, see if the target is in the A column. If it is check to see if it equals 'OK', then execute the macro on that row.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

On Error Resume Next
Dim TestRange As Range
Set TestRange = Target.Dependents

If TestRange.HasFormula And Err.Number = 0 Then
    If Left$(TestRange.Address, 2) = "$A" Then
        Application.EnableEvents = False

        If TestRange.Value = "OK" Then
            Call GreatMacro(TestRange)
        End If

        Application.EnableEvents = True
    End If
End If

End Sub

Answer 2

This page shows how you can set a macro to run on cell change.
Basically, the method is to over-use the worksheet change function, and if the cell is the correct one, you let the function run.

Try to keep as much as possible inside the if-this-is-correct-cell check to make sure that too much code isn't executing every single time you make a worksheet change.