Run code on formula change

[Spartan300]

Hi,

Hope someone can help. I have the following code...

Private Sub Worksheet_Calcualte(ByVal Target As Range)
Dim Cell As Range
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
    For Each Cell In Target.Cells
        With Cell
            If .Value = "Yes" Then
                .Offset(, 2).FormulaR1C1 = Date
            Else
                .Offset(, 2).ClearContents
            End If
        End With
    Next Cell
    Application.EnableEvents = True
End Sub

If .Value = "Yes" Then

relates to the formula =IF('Field Sales Manager'!$R$201>Commission!B14,"Yes","No"). The problem I have is that because its a formula, its not recognising the "Yes" and the code will not run.

So my question is, using the formula can I get the code to run?

Thanks

 

[VoG]

Worksheet_Calculate (note spelling!) doesn't take a Target argument. What range of cells do you want to check?

 

[lenze]

The Calculate Event (check your spelling) does not set a Target range. So,

Private Sub Worksheet_Calculate()
Dim cl as Range
Application.EnableEvents = False
For Each cl in Range("$C$2:$C" & Range("$C$65536").End(xlUp).Row)
    If cl ="Yes" Then
        Cells(Cl.Row,5) = Date
    Else: Cells(cl.Row,5).ClearContents
    End If
Next cl
Application.EnableEvents = True
End Sub

What are you really trying to do? There might be a better way.
lenze

 

[VoG]

It would be something like

Private Sub Worksheet_Calculate()
Dim Cell As Range
    Application.EnableEvents = False
    For Each Cell In Range("C1:C100")
        With Cell
            If .Value = "Yes" Then
                .Offset(, 2).Value = Date
            Else
                .Offset(, 2).ClearContents
            End If
        End With
    Next Cell
    Application.EnableEvents = True
End Sub

 

[Spartan300]

Thanks for the reply.

The target range is the whole of Column C I want the date to be entered into column E if Column C formula = Yes.

Thanks

 

[lenze]

OK. Both codes will do that. For grins, however, how are 'Field Sales Manager'!$R$201 and Commission!B14 cHanged? You might be able to use a WorkSheet_Change Event.
lenze

 

[Spartan300]

Thanks very much for your suggestions and help.

I tried this yesterday and it wouldn't work? but now it is perfect.

Thanks