Try this:
    Dim rngFound As Range
    With Worksheets("Paste Artwork Matrix")
        Set rngFound = .Range("G1:G10000").Find(What:=tb9.Value, After:=.Range("G1"), LookIn:=xlFormulas, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)
    End With
    If rngFound Is Nothing Then
        MsgBox tb9.vlaue & " not found."
    Else
        With TB10
            .Value = rngFound.Offset(0, 2).Value
            .Visible = True
        End With
        TB10Label.Visible = True
    End If