Execute a macro based a text in a column which is constantly changing by formulae

[vijayamani1987]

hi people,

Thanks in advance, and excuse me for my average English.

I am very new to VBA. I have a excel sheet, where cells in column B will change on predefined time frame, ex.5 minutes. I want to execute a macro when any cell in that column comes to have specific text, say YES. Please help me out with this.

Note: The values will change only by formula

 

[Grand]

Hi,
See if you can use this. You need to put this code in the sheet where your column "B" is located.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRng As Range
  With Range("B:B")
    Set MyRng = .Find(What:="Yes", _
       After:=.Cells(.Cells.Count), _
                  LookIn:=xlValues, _
                   LookAt:=xlWhole, _
             SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
                 MatchCase:=False)
        If Not MyRng Is Nothing Then
           MsgBox "My range contains YES!"
           'here you can call you macro
        End If
    End With
End Sub

 

[vijayamani1987]

Hi Grand,

Thanks for the work. I tried this code. But it is not calling my macro, and not showing any error. I tried to check it in dubug. "Step into" is not working in this macro.
Please check it.

 

[My Aswer Is This]

You earlier said:
Note: The values will change only by formula

The script in post 2 will not activate on formula change.
It will only activate when a manual change is made to some cell in column B

You try it and see.
Manually enter Yes into column B and you will see.

​

 

[vijayamani1987]

Hi "My Answer Is This",

Thanks for the reply. And my question is how to make it activate when any cells in column B changed by formula?

 

[vijayamani1987]

Hi "My Answer Is This",

Thanks for the reply. And my question is how to make it activate when any cells in column B changed by formula?

 

[My Aswer Is This]

I'm not really sure about how to do that. And sense it will be happening often.

There are others here on the forum who may provide a answer we can both learn from.

I will continue to monitor this thread.

 

[Peter_SSs]

I assume that the column B formulas are all similar?
Can you post one of the formulas (say from B2)?
Is that formula then just copied down column B?
Are there any formulas on the sheet besides those in column B?

 

[Grand]

I see that I overlooked "changed by formula" part
In that case, can "Calculate" event be trigged? If so,then we need to define the stuff in this event instead of Worksheet_Change event:

[FONT=Courier New]Private[/FONT] <code class="vb keyword">Sub</code> <code class="vb plain">Worksheet_Calculate()

End Sub</code>

This is getting interesting

 

[MARK858]

Have you tried it?