Found Cell Color And Worksheet ViewingSolved/Closed

Question

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

rizvisa1 · Answer

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.

TrowaD · Answer

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

Found Cell Color And Worksheet Viewing

2 responses

Similar discussions

Subscribe To Our Newsletter!