Run macro if cell (formula) in column meets condition

[DirkPH]

Hi All,

I am working on a work order tool where I have in column (P) a formula that pops up different phases/stages.
Now I am trying to start a macro automatically when the stage changes to "Gereed". I have tried a couple of things like worksheet_change and worksheet_calculate, but so far no luck, none triggered my macro to start.

I am out of ideas. Do you have any idea on how to set this macro?

Thanks!
BR, Dirk

 

[JW00]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("P:P")) Is Nothing Then
        If Target = "Gereed" Then
           Call YourMacroName
        End If
    End If

End Sub

 

[DirkPH]

Thank you JW00

I have tried tour code but it only works if cell is changed manually and not by formula. Is it correct? The cell has a calculated value so it had to see the formula…

BR, Dirk

 

[JW00]

As far as I know the change event is fired off the Active cell only, a possible work around is use the column that fires the changes column "P". Say you manually enter data in column "C" & that changes column "P" then you could try the following code, one caveat is that you have press enter on the cell you change in column "C" so that the offset is aligned, obviously you will have to change the target column & offset to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If ActiveCell.Offset(-1, 13) = "Gereed" Then
           Call YourMacroName
        End If
    End If

End Sub

 

[Joe4]

Thank you JW00

I have tried tour code but it only works if cell is changed manually and not by formula. Is it correct? The cell has a calculated value so it had to see the formula…

BR, Dirk

Yes, a Worksheet_Change event procedure is only fired when a cell is MANUALLY changed, and does not pick up things changed by formula.
If the formulas reference other cells in your workbook that are changed manually, you may be able to change it to fire off when those are changed (it can monitor changes happening in one cell, and check for changes in another, if there is a definable relationship).

Otherwise, a Worksheet_Calculate event procedure runs whenever a calculation is done (i.e. a formula value changes). However, the big caveat is that it cannot tell which cell changed - it just knows that some calculation ran somwhere on the worksheet. So you could check to see if some cell values currently equals "Gereed", but you cannot easily tell if some value was something else, and then just changed to "Gereed". There are more complex ways that may work, i.e. tracking changes, but it really depends on all the details and what else is on the sheet that may be changing too.

 

[DirkPH]

Clear statement. As suugested I will go for a manual cell that will change as well. It's a short workaround but just as effective.
Thanks guys.

 

[JW00]

Thanks, good to hear it's working for you.