VBA To Change Found Cell ColorClosed

Question

Hello, 
I would like to have the active cell to change color to yellow when a search value in a worksheet is located.The code I'm applying(below) seem to move my worksheet window to cell(A1) whenever a search value was found.Iwould like only the active cell  to move to my found value in the worksheet without changing my viewing of the whole worksheet.

Code I'm using,

Sub Find_Data()
    Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter Staff Number")
    If Trim(FindString) <> "" Then
        With Sheets("B2JT").Range("A:Z")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nil"
            End If
        End With
    End If

End SubCan somebody help me please.

venkat1926 · Answer

your macrfo is ok .  it goes to rng only ----only part of the screen is shown on the monitor  to avoid this selected view I have slightly modified and now you sese

Sub Find_Data()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter Staff Number")
If Trim(FindString) <> "" Then
With Sheets("B2JT").Range("A:Z")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Rng.Interior.ColorIndex = 6
MsgBox Rng.Address
Else
MsgBox "Nil"
End If
End With
End If
Worksheets("b2jt").Activate
Range("A1").Select
End Sub

VBA To Change Found Cell Color

1 response

Similar discussions

Subscribe To Our Newsletter!