Previous Topic Next Topic

Found Cell Color And Worksheet Viewing

Solved/Closed
azmiismail
Posts
17
Registration date
Thursday March 3, 2011
Status
Member
Last seen
July 20, 2011
 - Mar 12, 2011 at 02:04 PM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Mar 14, 2011 at 10:26 AM
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
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Mar 14, 2011 at 10:20 AM
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.
0
Answer 2 / 2
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Mar 14, 2011 at 10:26 AM
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
0