Previous Topic Next Topic

Found Cell Color And Worksheet Viewing

[Solved/Closed]
Report
Posts
17
Registration date
Thursday March 3, 2011
Status
Member
Last seen
July 20, 2011
 -
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
 -
Hello,
The code below had cause the found value cell color to remain there even after I closed the Input Box.I would like to have the color to be remove once I close the Input box.The second thing is that my viewing of the whole worksheet had been affected as the found cell is located at the top left corner hence limited me from viewing the whole worksheet.

Code

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

Please help me out.Thanks

2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
I dont see where this does any coloring. I think you are refering to the answer that Venkat provided earlier. If you compare this macro with the updated one, you will find where the color is happening. if you use macro recorder, and then manually removed the color from the cell, you will get the code for removal of the color.
Answer 2 / 2
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
 484
Hi azmiismail,

When you want the screen to stay at top left position and remove cell color once inputted value has been found try this: 

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
ActiveCell.Interior.ColorIndex = xlNone
Else
MsgBox "Nil"
End If
End With
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
End Sub

Does this yield the desired result?

Best regards,
Trowa
Ask a question