Macro - Excel won't scroll down to highlighted cell

[rojoe1]

Hi Guys - I wrote a Macro that will highlight a cell when the contents of the cell matches the searching criteria. The macro works fine, except that if the cell being searched is in row 200, Excel won't scroll down to where the cell is. For example. I have a spreadsheet that contains 290 rows. Let's say I want to find the content of cell 250, the Macro does highlight the cell, but Excel won't scroll down to where the cell is. I have to manually scroll down in order to find the cell. Can someone please help me find out how to get around this issue. Any help will be greatly appreciated.

I'm running Excel 2016 on a Windows 10 environment. Below is the Macro code.

Sub SearchAndHighlight()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ThisArea As Range, SearchMe As String
Sheets("Master List").Activate
SearchMe = InputBox("Write here what you're looking for.")
If IsEmpty(SearchMe) Then Exit Sub
Set ThisArea = Sheets("Master List").Cells.Find(SearchMe, lookat:=xlWhole)
If ThisArea Is Nothing Then
MsgBox SearchMe & " - " & "There is no such entry."
Else
Cells.Interior.ColorIndex = xlColorIndexNone
ThisArea.Interior.ColorIndex = 6
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

thisarea.Interior.ColorIndex = 6
[COLOR=#0000ff]Application.Goto thisarea, True[/COLOR]
End If

 

[Rick Rothstein]

Hi Guys - I wrote a Macro that will highlight a cell when the contents of the cell matches the searching criteria. The macro works fine, except that if the cell being searched is in row 200, Excel won't scroll down to where the cell is. For example. I have a spreadsheet that contains 290 rows. Let's say I want to find the content of cell 250, the Macro does highlight the cell, but Excel won't scroll down to where the cell is. I have to manually scroll down in order to find the cell. Can someone please help me find out how to get around this issue. Any help will be greatly appreciated.

I'm running Excel 2016 on a Windows 10 environment. Below is the Macro code.

Sub SearchAndHighlight()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ThisArea As Range, SearchMe As String
Sheets("Master List").Activate
SearchMe = InputBox("Write here what you're looking for.")
If IsEmpty(SearchMe) Then Exit Sub
Set ThisArea = Sheets("Master List").Cells.Find(SearchMe, lookat:=xlWhole)
If ThisArea Is Nothing Then
MsgBox SearchMe & " - " & "There is no such entry."
Else
Cells.Interior.ColorIndex = xlColorIndexNone
ThisArea.Interior.ColorIndex = 6
ThisArea.Show
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Does adding the red highlighted line of code above do what you want?

 

[rojoe1]

You're the best. It works beautifully.

 

[rojoe1]

Yes, thank you very much for your help.

 

[rojoe1]

It worked. Thank you very much for your help.

 

[Fluff]

Glad we could help & thanks for the feedback