Excel - buscar plabra no exacta en otra columna

Option Explicit

Sub Buscar_Referncia()

    Dim Text_A As String, Fila_A As Long, Fin As Boolean, _

        Text_B As String, Fila_B As Long

    Columns("C:C").Select

    Range("C49").Activate

    Selection.ClearContents

    Range("C1") = "Referencia"

    Range("C2").Select

    Fila_B = 2

    While Cells(Fila_B, "B") <> ""

        Text_B = Cells(Fila_B, "B"): Fila_A = 2: Fin = False

        While Cells(Fila_A, "A") <> "" And Not Fin

            Text_A = Cells(Fila_A, "A")

            If InStr(UCase(Text_A), UCase(Text_B)) > 0 Then

               ' ---&---- Marca de amarillo la celda de la Columna A

'               Range("A" & Fila_A).Select

'               With Selection.Interior

'                   .Pattern = xlSolid

'                   .PatternColorIndex = xlAutomatic

'                   .Color = 65535

'                   .TintAndShade = 0

'                   .PatternTintAndShade = 0

'               End With

'            

               ' ---&---- Marca de amarillo la celda de la Columna B

'               Range("B" & Fila_B).Select

'               With Selection.Interior

'                   .Pattern = xlSolid

'                   .PatternColorIndex = xlAutomatic

'                   .Color = 65535

'                   .TintAndShade = 0

'                   .PatternTintAndShade = 0

'               End With

               ' ---&---  Pone la referencia en la columna C

               Cells(Fila_B, "C") = "A" & Fila_A

               Fin = True

            End If

            Fila_A = Fila_A + 1

        Wend

        Fila_B = Fila_B + 1

    Wend

    MsgBox "Fin de la Macro"

End Sub