Macro Misfiring

[jrarmstrong]

Hi,


I am trying to put together a macro to automatically trigger a message box when the result of a formula is the word “Five”.


I have a small table B3:C13. In the B column a user enters text and the adjacent C column contains a VLOOKUP formula that inserts the word “Five” into column C if the criteria of the formula is met.


I have used the code below but this triggers when the text is entered into column B rather than when the criteria of the formula is met. Text will be entered in the morning and the formula looks up a range in another sheet where the data is imported periodically from an external source during the day. The message box should only fire when criteria is met.


Can anyone shed any light on what I am doing wrong?


Thanks,


James

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Cells.Count > 1 Then Exit Sub


    If Not Intersect(Target, Range("B3:C13")) Is Nothing Then


        If Target.Offset(, 1).Value = "In Play" Then


            MsgBox "Already used"


        End If


    End If


End Sub

 

[ParamRay]

.
.

Rather than using a Worksheet_Change event, you need to use a Worksheet_Calculate event.

(First, you'll need to make sure your Excel is set to automatic calculation mode.)

Try this code:

Private Sub Worksheet_Calculate()

    Dim Cell As Range

    For Each Cell In Me.Range("C3:C13")
        
        If LCase(Cell.Value) = "five" Then
            '
            '
            'more code goes here
            '
            '
        End If
    
    Next Cell

End Sub

 

[jrarmstrong]

Hi,

Thanks for this. It does not seem to trigger however. I have added in MsgBox "Five" and checked that automatic calc in on.

James

 

[ParamRay]

.
.

It should work fine.

Make sure you place the macro in the code module for that particular worksheet (and not in the worksheet containing your "lookup" range) and delete the existing Worksheet_Change sub procedure...