Excel - buscar texto en una macro

hola, como hago para buscar una cadena de texto en una macro de VB y que me aparezca un mensaje en un box que diga: "este nombre ya existe" o "Este apellido ya existe"?

deberías usar VBE (Herramientas >> Referencias... Microsoft Visual Basic for Applications Extensibility 5.3)
te dejo un ejemplo de algo que hice alguna vez... con alguna modificación ya tendrás lo que necesitás...

Option Explicit
'//By JuanC 2007
Sub CopyCodeToClipboard()
Dim d As DataObject
Dim i As Integer, s As String
Set d = New DataObject
s = GetCode("módulo3")
d.SetText s
d.PutInClipboard
Set d = Nothing
End Sub
Function GetCode(ByVal sModuleName As String) As String
Dim oVBC As Object
Dim Wb As Workbook
For Each Wb In Workbooks
For Each oVBC In Workbooks(Wb.Name).VBProject.VBComponents
If Workbooks(Wb.Name).VBProject.Protection = vbext_pp_none Then
If oVBC.Type = vbext_ct_StdModule Then
If LCase(oVBC.Name) = LCase(sModuleName) Then
GetCode = GetCodelines(Wb.Name, oVBC.Name)
GoTo fin
End If
End If
End If
Next
Next
fin:
Set oVBC = Nothing
Set Wb = Nothing
End Function
Private Function GetCodelines(ByVal wbk As String, ByVal VBComp As String) As String
Dim VBCodeMod As Object
Dim lPos&, j&, sCode$
On Error Resume Next
Set VBCodeMod = Workbooks(wbk).VBProject.VBComponents(VBComp).CodeModule
With VBCodeMod
lPos = .CountOfDeclarationLines + 1
Do Until lPos >= .CountOfLines
For j = 1 To .ProcCountLines(.ProcOfLine(lPos, vbext_pk_Proc), vbext_pk_Proc)
sCode = sCode & (.Lines(lPos + j, 1)) & vbCrLf
Next
lPos = lPos + .ProcCountLines(.ProcOfLine(lPos, vbext_pk_Proc), vbext_pk_Proc)
If Err Then Exit Function
Loop
End With
GetCodelines = sCode
Set VBCodeMod = Nothing
End Function

Saludos desde Baires, JuanC