Actually it was much easier than that. Turns out this works. Of course, FIND
& offset could also be used.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Employee info" or Sh.Name = "DB" Then exit sub
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Trim(Target.Text) = "" Then exit sub
MsgBox Application.VLookup(Target, Sheets("DB").Range("f1:g21"), 2, 0)
End If
End Sub
=====
Using FIND
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Employee info" Or Sh.Name = "DB" Then Exit Sub
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Trim(Target.Text) = "" Then Exit Sub
Set mv = Sheets("DB").Columns("F").Find(Target, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not mv Is Nothing Then
MsgBox mv.Offset(, 1)
Else
MsgBox "No Match"
End If
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JLatham" wrote in message
...
Don,
I'm assuming he probably did, or will, take you up on your offer. Take a
look at my earlier post and his reply to it. I think my code was almost
there, although you could use FIND vs the loop, it just needs to NOT exit
the
loop when it finds the 1st match, and it needs to work through several
worksheets ... which I think his original code may already do.
Jerry

"Don Guillett" wrote:

If desired, send your file to my address below. I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

Will not be able to look until later today
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"puiuluipui" wrote in message
...
Hi Don, i will have many names in J4J:10.
Ex
J4=ANNE K4=A MESSAGE 1
J5=JOHN K5=A MESSAGE 2
J6=JIM K6=A MESSAGE 3
J7=CRIS K7=A MESSAGE 4

When i write a name in range B5:B120, in any sheet except sheet
"PLANNING"
and "SEARCH", then the macro to search for that name in sheet
"PLANNING"
in
range J4J:10 and to display the message next to that name. Message is
located
in next colum, in K4:K10.
The macro i have, (in my first post) is very good, except that it
display
a
message for every name, and the message is inside macro. I have a list
with
names and i need to display a message for every name. And i need to
have
names and their message in a table so, i can modify them as needs.
Can this be done?
Thanks!

"Don Guillett" wrote:

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!