highlite find cell?

[dani1]

hi experts!

is any body knows that if we use find option in excel so highlite found cell?

is it possible?

 

[Jaafar Tribak]

hi experts!

is any body knows that if we use find option in excel so highlite found cell?

is it possible?

What do you mean by highlight the found cell ? the target cells are automatically selected as they are found and the selection highlights them .
...or do you want to change something like the found cells interior color for better visibility ?

 

[dani1]

i want it to change for better visibilityis it possible?

 

[Jaafar Tribak]

i want it to change for better visibilityis it possible?

What excel version do you have ?

 

[dani1]

excel 2007

 

[Jaafar Tribak]

Try something along these lines :

(Place the code below in the worksheet module where you are performing the Find operations)

'XL 2007

Option Explicit

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private oPrevTarget As Range
Private lColIndx As Long
Private bFBold As Boolean
Private lFSize As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Searching Then
        On Error Resume Next
        Call RestoreInitProps(oPrevTarget)
        Set oPrevTarget = Target
        Call StoreInitProps(oPrevTarget)
        Call HighlightRange(oPrevTarget)
    Else
        Call RestoreInitProps(oPrevTarget)
        Set oPrevTarget = Nothing
    End If

End Sub

Private Function Searching() As Boolean

    Searching = CBool(FindWindow _
    ("bosa_sdm_XL9", "Find and Replace"))

End Function

Private Sub RestoreInitProps(Target As Range)

    With Target
        If Not Target Is Nothing Then
            .Interior.ColorIndex = lColIndx
            .Font.Bold = bFBold
            .Font.Size = lFSize
        End If
    End With
    
End Sub

Private Sub StoreInitProps(Target As Range)

    With Target
        lColIndx = .Interior.ColorIndex
        bFBold = .Font.Bold
        lFSize = .Font.Size
    End With
    
End Sub

Private Sub HighlightRange(Target As Range)

    Beep
    With Target
        .Interior.ColorIndex = IIf(.Interior.ColorIndex = 3, 4, 3)
        .Font.Bold = True
        .Font.Size = .Font.Size + 4
    End With

End Sub

 

[dani1]

thanx bro bt tell me where i ve to put dis code in module?or in sheet1 code window with wat property?

 

[VoG]

Or try this in a standard module

Sub FindHighlight()
Dim tempcell As Range, Found As Range, sTxt, FoundRange As Range, Response As Integer
Set Found = Range("A1")
sTxt = InputBox(prompt:="Enter value for search")
If sTxt = "" Then Exit Sub
Set tempcell = Cells.Find(What:=sTxt, After:=Found, LookIn:=xlValues, lookat:=xlWhole)
If tempcell Is Nothing Then
    MsgBox prompt:="Not found"
    Exit Sub
Else
    Set Found = tempcell
    Set FoundRange = Found
End If
Do
    Set tempcell = Cells.FindNext(After:=Found)
    If Found.Row >= tempcell.Row Then Exit Do
    Set Found = tempcell
    Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Interior.ColorIndex = 6
Response = MsgBox(prompt:="Clear highlighting", Buttons:=vbOKCancel + vbQuestion)
If Response = vbOK Then FoundRange.Interior.ColorIndex = xlNone
End Sub

 

[dani1]

thanx both of u,if i need some more help i ll talk to u guyz.

 

[XL&ME]

Vog,

Thank you for the the above macro.

Is it possible to modify the macro so that it will highlight any cells if they contains any part of the serach value?

Thanks