What is an op-ed column?

Auto shading of column and row when in a cell

• QUESTION: Doeshave an option to auto-shade the column and row for the cell that a user is working in? I work with large spreadsheets and sometimes enter information in the wrong column. It would help if there column and row were highlighted so that I could confirm that I am enterin the info in the right spot. ANSWER: The following code should hilite the working row and column. I deliberately did not hilite the first row and column because my code clears existing fills. Many people use color fills on the first row--and sometimes the first column--on their sheet and I didn't want my code to mess that up. If you have color fills elsewhere on your sheet I could add code to keep from clearing that, but that would be fairly complicated. So I hope that is not the case. Here is the code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static LastSelection As Range If Not LastSelection Is Nothing Then With LastSelection 'unhilite row from column B on Range(Cells(LastSelection.Row, 2), Cells(LastSelection.Row, 256)).Interior.Color = xlNone 'unhilite column from row 2 on Range(Cells(2, LastSelection.Column), Cells(65536, LastSelection.Column)).Interior.Color = xlNone End With End If 'hilite row from column B on Range(Cells(Target.Row, 2), Cells(Target.Row, 256)).Interior.Color = RGB(220, 255, 255) 'hilite column from row 2 on Range(Cells(2, Target.Column), Cells(65536, Target.Column)).Interior.Color = RGB(220, 255, 255) 'Save last selection Set LastSelection = Target End Sub This code must be placed in the worksheet's Code module. To do that, right-click on the worksheet's tab, select View Code, and paste my code into the Code pane. Let me know how this works for you. ---------- FOLLOW-UP ---------- QUESTION: Here is an update to the follow-up question sent earlier. I tried the code on a newer version of Excel. Don't mock me, but my computer at work has2003. When I tried the code init worked just as I wanted it to, however, there were two issues: 1) The previous coloring I had on the spreadsheet is erased as the new shading moves from cell to cell. Any chance that this can be avoided? 2) When I save my spreadsheet I cannot turn off the shading so it saves where I last left it. Then when I open the sheet again a new shading starts up. Sorry for the complicated questions. However, your solution did work so I am hopeful there is a way to make this work. MM

• Answer:

  Sorry. I used the Color property in two places where I should have used the ColorIndex property. Here is the repaired code. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static LastSelection As Range If Not LastSelection Is Nothing Then With LastSelection 'unhilite row from column B on Range(Cells(.Row, 2), Cells(.Row, 256)).Interior.ColorIndex = xlNone 'unhilite column from row 2 on Range(Cells(2, .Column), Cells(65536, .Column)).Interior.ColorIndex = xlNone End With End If 'hilite row from column B on Range(Cells(Target.Row, 2), Cells(Target.Row, 256)).Interior.Color = RGB(220, 255, 255) 'hilite column from row 2 on Range(Cells(2, Target.Column), Cells(65536, Target.Column)).Interior.Color = RGB(220, 255, 255) 'Save last selection Set LastSelection = Target End Sub