Search for a text string & highlight the cell

[Ashish Batham]

I want to search for the text in an excel spreadsheet with help of an input box.

Once the text is found that cell should be highlighted

Also, all the cell should be highlighted which has that text

if not match found a message should prompte "No match found"

Thanks.

I have created a codem but unfortunately not working

Sub snsearch()

Dim Rng As Range
Dim c As Range
Dim varUserInput As Variant
On Error GoTo err_handler

varUserInput = InputBox("Please enter the Serial number you wish to find", _
"Laser Serial Number", "")
Set Rng = Range("J3:J" & Range("J65536").End(xlUp).Row)

Cells.Find(What:=varUserInput, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

Exit Sub

err_handler:
MsgBox "No Matching Serial Number has been found, please try again! "

End Sub

 

[Luke M]

I think this is what you are looking for:

Sub snsearch()

Dim Rng As Range
Dim c As Range
Dim varUserInput As Variant
On Error GoTo err_handler

varUserInput = InputBox("Please enter the Serial number you wish to find", _
        "Laser Serial Number", "")
Set Rng = Range("J3:J" & Range("J65536").End(xlUp).Row)

'Since we defined a range, we want to use the Find method on that Range
'I am presuming, and we need to make sure that the first cell we start
'looking in is somewhere within that range
Rng.Find(What:=varUserInput, After:=Rng.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
    .Interior.ColorIndex = 3

'Note that instead of activating the cell, we change the ColorIndex
'Which "highlights" the cell
Exit Sub

err_handler:
MsgBox "No Matching Serial Number has been found, please try again! "

End Sub

 

[Ashish Batham]

Hi Luke,

Thanks for the reply.

Attached is the file with macro in it.

Wherever the match is found the cell is highlighted, however if the match is not found the macro shows an error

Request you to help me in this

Thanks & Regards,

 

[oldchippy]

@Ashish Batham

Your file didn't get attached?

 

[Ashish Batham]

tried again

 

[Ashish Batham]

not getting attached.....will send you the coding...hope this is fine

 

[Ashish Batham]

just noticed, not able to upload the file....below is the macro for your reference.......
whenever a match is not found, macro gives an error message
------------------------------------------------------------------------------------

Sub Macro1()
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
Selection.ClearContents

Dim A, B, X, Y, Z

Z = 0


A = InputBox("Please Enter the Third Party Name")

Dim i As Long
Dim Fnd As String
Dim fCell As Range
Dim ws As Worksheet

Application.ScreenUpdating = False

Fnd = InputBox("Find what:", "Find and Highlight", "Enter text")
If Fnd = "" Then Exit Sub


B = Cells.Find(what:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

' If Not B Is Nothing Then
' MsgBox ("Third Party Details are NOT Available in Database")
' End If

' **************

' Get first cell address after cell

Y = ActiveCell.Address
' MsgBox (Y)
' **********************

' Create loop to search next entries and format those cells

Do

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With

X = Cells.Find(what:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

B = ActiveCell.Address

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With

Z = Z + 1

Loop Until B = Y

Range("B2") = "Found " & Z & " Entries"
MsgBox ("Found " & Z & " Entries")
Range("A1").Select
' ********************



End Sub

 

[Luke M]

The macro you just posted does not have the errHandler that your original example had. You need to make sure you have an error trap somewhere before the Find method occurs.