Modify to suit
Sub findtext()
For i = 1 To Sheets.Count
MsgBox Sheets(i).Name
Set myfind = Sheets(Sheets(i).Name).Columns("j") _
..Find(What:="john", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not myfind Is Nothing Then
myfind.Offset(, 1) = "hello"
'MsgBox myfind.Row
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"puiuluipui" wrote in message
...
Hi, i have this range:
B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i
will write "John". after i click enter the macro is searching in J4:J10
for
"John" and the macro display the message.
I need the macro to display the message next to "John" in range K4:K10.
J4:J10 and K4:K10 are in "PLANNING" sheet.

Ex:
In B5 i write John
J7=john
K7= No colaboration.
I need the macro to display a msgbox with message from the cell next to
John. John in J7, message is in J7.

If the macro find John in another cell, let's say J9, then to display
message from K9.
In every cell range K4:K10, i will have another message
In range J4:J10, there will be no duplicates.
I need to extract message from range.

Can this be done?
Thanks!

"puiuluipui" a scris:

Hi, i have this macro:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then
If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then
If Trim(Target.Text) < "" Then
Application.EnableEvents = False
If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _
Split(Target.Value)(0)) 0 Then
MsgBox "NO COLABORATION", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
Application.EnableEvents = True
End If
End If
End If
End Sub

I need to extract "NO COLABORATION" from range K4:K10. I don't want to
have
"NO COLABORATION" in macro. i need to transform this:
MsgBox "NO COLABORATION", vbExclamation, ""
into something like this:
MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _

Can this be done?
Thanks!