VBA Code to Find then Highlight Cell that is Found, then Revert to No Fill when pressing Tab (Could be any key)

[Rforce4]

I have a code that involves a search bar using a module linked to a text box to find a cell containing an item number in a data set. When you click on the text box “Search”, it brings you to the first item in a range for Column A that matches what is typed into Cell B1. I have another code on the sheet itself that activates when Tab key is pressed, it brings you back to Cell B1, the “search field” Cell. So far this works great and as intended.

What I am trying to add that I am having difficulty with is having the cell that is found using the “search” module to be highlighted when it is selected, then when tab key is pressed, the color is reverted back to No Fill before, during or after it selects cell B1. I can get it to change the color of the desired cell when the found cell is selected, but I can’t work out the second part of the issue: the color going back to no fill on Tab press.

Does anyone have a code that code implement this?

 

[Micron]

Seeing your code would help as there's no way to know if you're using SelectionChange event or what for the Tab key code.
Unless you can rely on the prior cell being 1 cell to the left you can't be sure if using Offset(0,-1) will work. You may need to set a public (global) variable to whatever the last active cell was each time there is a selection change. However, that will only be set after a change is made, thus it would not work correctly the first time. Thus you'd set that value during workbook open event, then the variable has a value right away.

You might also find that if user holds down the tab key too long, things will not work as they should. You could test that.