Here is a technique for having a temporary color band appear in the current row and column. It uses conditional formatting and just a touch of VBA, and is fairly easy to implement.
First, select a worksheet where you want to use this technique. Next, select all cells on the worksheet. (The easiest way to do this is to click on the box just above row 1 and to the left of column A.) Then select Format/Conditional Formatting from the menu. For Condition 1, choose Formula Is, and then type in the box:
=OR(AND(CELL("row")=ROW(),COLUMN()<=CELL("col")),AND(CELL("col")=COLUMN(),ROW()<=CELL("row")))
[Note: it’s easier to copy this formula to the clipboard, then paste it from the clipboard into the Formula Is box.] Then press the Format button, and choose a color under the Patterns tab. (My first preference is light yellow.) Then press the OK button.
Now press Alt-F11 to open the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing, if necessary. Then double-click the item labeled Sheet1 (or whatever name your worksheet is). Then enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub
Switch back to the worksheet, and move around. You should now have row and column bands of light yellow (or whatever color you chose) wherever you move the cell pointer!
Applicability and limitations of this technique:
I have used this technique successfully in Excel 97 and Excel 2000, with Windows 98, Windows NT4, and Windows XP operating environments. I don’t know if it will work in Excel 2002, although I hope it does.
First limitation: you can’t use the F8 key to extend a range selection, though Shift-F8 works fine if you hold down the Shift key. Second limitation: you do surrender one conditional format, if this is important to what you are trying to accomplish on a particular worksheet. If so, check out this link (suggested by Ivan Moala): http://www.xcelfiles.com/Excel02.html#AnchorLink-1
Additional Considerations:
If you want to highlight only the current cell as you move about the worksheet, and not the row and column, modify your conditional formatting statement to read:
=AND(AND(CELL(“row”)=ROW(), COLUMN() . . . .
instead of starting with
=OR(AND(CELL(“row”)=ROW(), COLUMN() . . . . .
Also, to turn off the bands (for printing purposes, say), you need to make a couple of adjustments. Select all cells again, and modify Conditional Format 1 to read this way:
=AND($A$1<>””, OR(AND(CELL(“row”)=ROW(), COLUMN() . . . . .)))) [Note the four close parentheses at the end of this formula construction.]
Then adjust the VBA code to read:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If [A1].Value <> "" Then
Application.ScreenUpdating = True
End If
End Sub
Now, if cell A1 is blank, the cell highlighting is turned off. Of course, you can choose some cell other than A1 if you want, or devise some other test. Just make sure that you change the references appropriately.
Tom McClain
Round Rock, TX