When Tab or Enter Is Hit, Change The Cell Color of (previously) Selected Cell

[ExcelBean]

Is there a way to make the cell color change every time you hit tab or enter?

Thanks in advance!

 

[portews]

One way to do this is to get a Tab or Enter key press with the Application.OnKey function, send it to some code to color the cell and move the cursor.
The only thing that may be a little different is that with normal Tab/Enter keys, if you tab to the right and hit enter, it acts more as a carriage return, selecting the cell one row down and under the original cell before you hit the tab. The code below only makes the cursor move to the right on enter.

I put this code in the worksheet I want to know about. It could also be put in a Workbook_Activate sub to look at the whole workbook, just make sure to put the corresponding deactivation code in the right place so it resets when you're done.

Private Sub Worksheet_Activate()
[COLOR=#00ff00]'keypad Enter key[/COLOR]
Application.OnKey "{ENTER}", "EnterKey"
[COLOR=#00ff00]'keyboard Enter key[/COLOR]
Application.OnKey "~", "EnterKey"
Application.OnKey "{TAB}", "TabKey"
End Sub


Private Sub Worksheet_Deactivate()
[COLOR=#00ff00]'resets the key function[/COLOR]
Application.OnKey "{ENTER}"
Application.OnKey "~"
Application.OnKey "{TAB}"
End Sub

Put this in a module:

Sub EnterKey()
[COLOR=#00ff00]'set the color to green[/COLOR]
ActiveCell.Interior.ColorIndex = 4
[COLOR=#00ff00]'move the cursor down one row[/COLOR]
ActiveCell.Offset(1, 0).Select
End Sub


Sub TabKey()
[COLOR=#00ff00]'set the color to green[/COLOR]
ActiveCell.Interior.ColorIndex = 4
[COLOR=#00ff00]'moves the cursor right one row[/COLOR]
ActiveCell.Offset(0, 1).Select
End Sub