The Question: In order to help visually locate the active cell pointer, is there a way to have a temporary color band appear in the current row and column?
The solution is an elegant solution to this question. Important: This solution makes use of Conditional Formatting, and will overwrite any conditional formats that you have on the worksheet. Do not use this method if you already have conditional formats on the worksheet.What it does:- Ivan's code will highlight the current row and column, up to the cell pointer in a light yellow color.
- As you move to a new cell, the highlights move with the cell pointer.
- If the cell pointer moves to a cell that is already yellow, the highlights change color
This effect is accomplished by using the Worksheet_SelectionChange event handler. The following code must be pasted on to the Worksheet code module. If you don't understand the difference between a regular module and the Worksheet module, review Tip 55. Every time that the cell pointer is moved to a new location, the code will delete all conditional formats on the worksheet, and assign a new conditional format on the fly to the cells in the current row and column.There are two drawbacks to this method. First, as mentioned above, it is not appropriate if you already have conditional formats. Second, the code tends to clear the clipboard, so it becomes virtually impossible to copy and paste while this code is running.Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iColor As Integer '// Amended routine found on this Web site '// Note: Don't use IF you have Conditional '// formating that you want to keep! '// On error resume in case '// user selects a range of cells On Error Resume Next iColor = Target.Interior.ColorIndex 'Leave On Error ON for Row offset errors If iColor < icolor =" 36" icolor =" iColor" icolor =" Target.Font.ColorIndex" icolor =" iColor" colorindex =" iColor" colorindex =" iColor">Adapted from this thread on the message board
No comments:
Post a Comment