How to use VBA code to check a range of cells for a text

Requirement:

The user wants to check a range of cells for a text, text is in one of the cells, and wants excel to be able to identify which row the text is in. Range is from columns "AB:BB".

The user just put 500 in the loop just to count down the rows. The range is not beyond row 500.

Here is the user's current code:

Code:

Sub FindMatchingText()
Dim i As Integer, TextToFind As String

TextToFind = "CA5"

For i = 1 To 500
       If Sheets("Hotel Booking").Cells(i, "AB" + i, "BB").Value = TextToFind Then
            MsgBox ("Found text on row" & i)
            Exit Sub
       End If
Next i

MsgBox ("Text not found")

End Sub

Solution:

Code:

Option Explicit

Sub FindMatchingText()
Dim i As Integer, TextToFind As String
Dim lr As Long, c As Range, rng As Range
Dim ThisRow As Long
lr = Range("AB" & Rows.Count).End(xlUp).Row
Set rng = Range("AB1:BB" & lr)
TextToFind = "CA5"

For Each c In rng
If InStr(c, TextToFind) > 0 Then
ThisRow = c.Row
MsgBox ("Found text on row" & ThisRow)
Exit Sub
End If
Next c

MsgBox ("Text not found")

End Sub

Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman.