Won't work because you used change event rather than calculate event.

See if this works for you

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "B:B"
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Me.Range(WS_RANGE)
If cell.Value = 1 Then
MsgBox cell.Address
End If
Next cell
ws_exit:
Application.EnableEvents = True
End Sub

Note: if you have more than one result of 1 in the range you will get
multiple msgbox's at each re-calc.

But it's a start<g


Gord

On Sat, 16 Aug 2008 10:09:01 -0700, kleysonr
wrote:

Gord,

I tested but dont work.
I many cells with formula SE(), so i need identify the source of event.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If (Target.Range("B" & Target.Cells.Row).Value = 1) Then
MsgBox (": " & Target.Row)
End If
End If
Application.EnableEvents = True
End Sub

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

Maybe wrap your row deleting inside sheet event code.

Private Sub Worksheet_Calculate()

If target cell value = True then

run the macro to delete your row or just code it inside this event

End Sub


Gord Dibben MS Excel MVP


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

Mike,

How can I resolv my problem ?

I have a formula that check if a value cell is True or False, if it's True a
need remove a specific row.

"Mike H" wrote:

Hi,

You can't. A function can only directly alter the cell it was called from
and a sub called from a function has the same limitations.

Mike

"kleysonr" wrote:

Hi

I've created a function that call a Sub to write some cells or delete a row.
But don't work.

If i call the Sub from a button it works fine.

I saw a post saying that a function can't change cells, only Sub can do
that. But i really need that a function call a Sub to delete a row for me
because i use that function in cells' formulas.

How can i do that ?