find and highlight searched data

Hi guys,

I am new to excel vba programming. I would like to ask for your help regarding highlighting a specified found data. I have a userform which contains a textbox and a find button. What I want to do is when I enter the employee ID on the textbox, it will pull up the searched data and will highlight the entire row where the searched data is located. I appreciate your help excel gurus.
Thanks

You may be able to apply the code from this post.

http://www.mrexcel.com/forum/showthr...hlight=firefox

It hightlights cell text as you type...

Thanks for the info Tom. I was able to try the sample code from the website you gave me. It works well however, I would like the entire row to be highlighted instead of just the cell. How can I do that?

Here is the code from the website:

Option Explicit

Private Type OriginalFormat
FontBold As Boolean
FontColorIndex As Integer
FontUnderLined As Boolean
Range As Range
CharsStart As Integer
CharsLength As Integer
IsInitialized As Boolean
RangeInteriorColorIndex As Integer
RangeInteriorPattern As Integer
End Type

Private CurrentSearchRng As Range
Private f As OriginalFormat
Private Const MakeBold As Boolean = True
Private Const Underline As Boolean = True
Private Const FontColorIndex As Integer = 3 'red
Private Const InteriorColorIndex As Integer = 6 'Yellow
Private Const InteriorPattern As Integer = xlSolid

Private Sub Workbook_Open()
Set CurrentSearchRng = ActiveSheet.UsedRange
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Set CurrentSearchRng = ActiveSheet.UsedRange
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set CurrentSearchRng = ActiveSheet.UsedRange
End Sub

Public Sub FindAndFormat(arg)
Dim r As Range, s As Integer, l As Integer

If (CurrentSearchRng Is Nothing) Then
Set CurrentSearchRng = ActiveSheet.UsedRange
End If

Set r = CurrentSearchRng.Find(arg, _
CurrentSearchRng(CurrentSearchRng.Cells.Count), , XlLookAt.xlPart)
If (Not r Is Nothing) Then
If f.IsInitialized Then
With f.Range.Characters(f.CharsStart, f.CharsLength).Font
.Bold = f.FontBold
.ColorIndex = f.FontColorIndex
.Underline = f.FontUnderLined
End With
With f.Range.Interior
.ColorIndex = f.RangeInteriorColorIndex
.Pattern = f.RangeInteriorPattern
End With
End If

If Len(Trim(arg)) = 0 Then
f.IsInitialized = False
Exit Sub
End If

'get current find and save original formats
s = InStr(UCase(r), UCase(arg))
l = Len(arg)
f.IsInitialized = True
f.FontBold = r.Characters(s, 1).Font.Bold
f.FontColorIndex = r.Characters(s, 1).Font.ColorIndex
f.FontUnderLined = IIf(r.Characters(s, 1).Font.Underline = 2, True, False)
f.CharsStart = s
f.CharsLength = l
f.RangeInteriorColorIndex = r.Interior.ColorIndex
f.RangeInteriorPattern = r.Interior.Pattern

'current find custom format
With r.Interior
.ColorIndex = InteriorColorIndex
.Pattern = InteriorPattern
End With
With r.Characters(s, l).Font
.Bold = MakeBold
.Underline = Underline
.ColorIndex = FontColorIndex
End With
Set f.Range = r
End If


End Sub

May be this will help you

Sub FindAndColour()
    Dim strSearch As String
    strSearch = "Value1"
    Dim rngSearch As Range
    Set rngSearch = Range(Sheets("Sheet1").Cells(2, 2), Sheets("Sheet1").Cells(15, 5))
    
    Dim i As Double
    Dim j As Double
    
    For i = 1 To rngSearch.Rows.Count
        For j = 1 To rngSearch.Columns.Count
            If rngSearch.Cells(i, j).Value = strSearch Then
                rngSearch.Cells(i, j).EntireRow.Interior.ColorIndex = 3 ' coloured in red
                MsgBox "Value found"
                Exit Sub
            End If
        Next
    Next
End Sub

Thanks for the code Joro. I have tried your code but it didn't work on my end. However, I got an idea from your sample code and tweak the previous code I have posted above. Now its working perfectly! Thanks for your help Tom and Joro
Here is the revised code from my previous post:
<code>
Public Sub FindAndFormat(arg)
Dim r As Range, s As Integer, l As Integer

If (CurrentSearchRng Is Nothing) Then
Set CurrentSearchRng = ActiveSheet.UsedRange
End If

Set r = CurrentSearchRng.Find(arg, _
CurrentSearchRng(CurrentSearchRng.Cells.Count), , XlLookAt.xlPart)
If (Not r Is Nothing) Then
If f.IsInitialized Then
With f.Range.Characters(f.CharsStart, f.CharsLength).Font
.Bold = f.FontBold
.ColorIndex = f.FontColorIndex
.Underline = f.FontUnderLined
End With
With f.Range.Interior
.ColorIndex = f.RangeInteriorColorIndex
.Pattern = f.RangeInteriorPattern
End With
End If

If Len(Trim(arg)) = 0 Then
f.IsInitialized = False
Exit Sub
End If

'get current find and save original formats
s = InStr(UCase(r), UCase(arg))
l = Len(arg)
f.IsInitialized = True
f.FontBold = r.Characters(s, 1).Font.Bold
f.FontColorIndex = r.Characters(s, 1).Font.ColorIndex
f.FontUnderLined = IIf(r.Characters(s, 1).Font.Underline = 2, True, False)
f.CharsStart = s
f.CharsLength = l
f.RangeInteriorColorIndex = r.EntireRow.Interior.ColorIndex
f.RangeInteriorPattern = r.EntireRow.Interior.Pattern

'current find custom format
With r.EntireRow.Interior
.ColorIndex = InteriorColorIndex
.Pattern = InteriorPattern
End With
With r.Characters(s, l).Font
.Bold = MakeBold
.Underline = Underline
.ColorIndex = FontColorIndex
End With
Set f.Range = r.EntireRow

End If


End Sub
</code>

I don't know why the code doesn't work for you - it works fine on my PC, but it's good you've managed to solve your problem.