- NEW!
by firecircle on October 5th, 2008
This solution will colour the entire row and column of the selected cell. Beware - it will remove all other cell shading, however, conditional formatting still works.
> Press ALT-F11 to open the VBA editor.
> Double click at the right on "ThisWorkbook"
> Paste in the following code, and exit, that's it.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.EntireRow.Interior.ColorIndex = xlColorIndexNone
OldCell.EntireColumn.Interior.ColorIndex = xlColorIndexNone
End If
Target.EntireRow.Interior.ColorIndex = 6
Target.EntireColumn.Interior.ColorIndex = 6
Set OldCell = Target
End Sub
-----
microsoft.com also has a solution:
http://office.microsoft.com/en-us/excel/HA011366231033.aspx, but it will wipe out conditional formatting.
by SpreadsheetsDirect on July 8th, 2008
If you press ctrl & space together it will highlight the column, shift & space will do a row.
by Babycakes Deux on May 31st, 2008
If you click on the gray letter at the top of the column or on the gray number to the left of the row, it will highlight the entire row or column. +5
by tortiecat on May 31st, 2008
When you put the cursor on the cell at the top or on the left-hand side, it should turn into a black arrow. When you click, the entire column or row should be highlighted.
by Anonymous on August 4th, 2009
re: firecircle & Cyanotic Wasp 's answers...
Works great, thanks!
Bit of a problem, though. If you try to copy a cell, when you click on the cell you want to paste to, it loses the "copy". Any suggestions?
by SpreadsheetsDirect on July 9th, 2008
Have you tried freezing panes (Windows - Freeze Panes)? It will keep whatever you freeze on screen while you move down the file e.g. if you select cell D2 and freeze panes columns A, B & C and row 1 will remain when you scroll down.
What is a grid source?
by Answerbag Staff on August 25th, 2010
| 1 person likes this
How do I use a Notes link in Excel?
by Answerbag Staff on August 19th, 2010
| 1 person likes this
How do i have data flow between spreadsheets?
by Answerbag Staff on August 19th, 2010
| 1 person likes this
In Excel, How do I sort multiple rows? I want to sort by the person's name, but keep the grade below the person's name.
by jisforjay on August 16th, 2010
| 1 person likes this
How spreadsheet meets a company's needs and its effectiveness in providing the information that company requires?
by All Stars on December 3rd, 2010
| 1 person likes this
You're reading On Excel, is there a way to have the entire row/column highlight when you click on a cell, instead of just the row number on the l.h. side and the column letter on the top?
Your favorite Q&A site is on Facebook! Become a fan to connect with the ever-growing community of Answerbaggers, share your thoughts and find out what Answerbag's up to next!
Comments
That is pretty slick. Thanks.
(Users should note that AB has hashed most of the appearances of the 'word' "ColorIndex" by inserting a space that Excel doesn't like or accept. Perhaps the re-paste below will fix that.)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.EntireRow.Interior.ColorIndex = xlColorIndexNone
OldCell.EntireColumn.Interior.ColorIndex = xlColorIndexNone
End If
Target.EntireRow.Interior.ColorIndex = 6
Target.EntireColumn.Interior.ColorIndex = 6
Set OldCell = Target
End Sub
by Cyanotic Wasp on October 7th, 2008
I would add a line to this code:
On Error Resume Next
immediately after the End If line.
The reason it's required is that if the worksheet is Protected, then the code will fail as it will not be permitted to change the ColorIndex.
I hadn't realized this until I tried to plug the code into an existing workbook that I use at work, which typically has protected worksheets.
by Cyanotic Wasp on October 8th, 2008