Found Cell Color And Worksheet Viewing [Solved/Closed]

Question

Previous Topic Next Topic

Found Cell Color And Worksheet Viewing [Solved/Closed]

Report

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: 2761
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 21, 2021

- 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

Found Cell Color And Worksheet Viewing
Vba change cell color based on value ✓ - Forum - Programming
How to change value based on cell color in excel ✓ - Forum - Office Software
Copy non blank cells to another worksheet vba ✓ - Forum - Excel
If statement to change cell color - Forum - Excel
Excel if cell color is red then - How-To - Excel

2 replies

Ask a question

rizvisa1 · Answer 1 · 2011-03-14T10:20:46-0400

Answer 1 / 2

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
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.

TrowaD · Answer 2 · 2011-03-14T10:26:53-0400

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 [Solved/Closed]

2 replies

Your reply

Subscribe To Our Newsletter!