Highlight row header

So, I have a worksheet that is large enough that when a user enters a value in a cell that is so far from the row header on column B, the users sometimes enter the value on the wrong row. Is there a way with VBA or conditional formatting to highlight a cell when another cell is clicked. For example, if the user clicks on AD107 before he/she enters a value that B107 is filled with certain color, once the mouse clicks elsewhere, like AC100, the fill in B107 goes away and B100 is highlighted/filled?

Why not freeze header panes?

Originally Posted by Billy Spivy

Why not freeze header panes?

Yes, that would help, but some users to scroll enough for the freeze to be effective.

Originally Posted by Mumps1

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. After you enter a value anywhere in the sheet and exit the cell, the corresponding cell in column B of that row will be selected for input. Will this work for you?

Please Login or Register  to view this content.

This seems simplistic and works, but if I can have the situation as I described would be ideal.

Wow, ProtonLeah, that worked like a champ. How do I make it work only in certain worksheets?

ProtonLeah, do you think you can modify the code so that once the user clicks in another tab that the previous highlight goes away? I noticed that when every tab that had a cell clicked, the columns and row highlight is remained.

Thanks, Leah. I just back checking these codes. Truly appreciate it. This almost works great for my application, except for the fact that I do have conditional formatting already built in that allows the user to search. It basically highlights cells that contain strings that are entered in a particular cell, so the Cells.FormatConditions.Delete doesn't work here. Is it possible not to delete the condition formatting for certain rows? The aforementioned cells that are highlighted are basically in row 1, 2, and 3?