VBA To Change Found Cell Color

[Closed]
Report
Posts
17
Registration date
Thursday March 3, 2011
Status
Member
Last seen
July 20, 2011
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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 Sub
Can somebody help me please.

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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