Excel 2010: How to Highlight Row and Column of Selected Cell

March 28, 2011 8:44 AM

Excel 2010- I need to highlight both the row and the column of the cell that is currently selected in my enormous spreadsheet. Can you help me accomplish this?
posted by Irontom to Computers & Internet (5 answers total)

This is not a single command for the selected cell, but on a Mac I can do this thusly:
Highlight the row by clicking the row number.
Depress and hold Command key.
Highlight column by clicking the column letter.
posted by beagle at 8:54 AM on March 28, 2011

Best answer: It depends why you want to "highlight" the row and column. If you're just trying to make it obvious where the currently selected cell is, there's a hacky way to do it here. Basically it's macro that is constantly updating the conditional formatting on the spreadsheet as you select different cells, so it's no use if you have any conditional formatting already.
posted by EndsOfInvention at 8:55 AM on March 28, 2011 [1 favorite]

Do you mean that you need to keep it highlighted, or just highlight it temporarily?

For the former, highlight the row and use the fill feature. Do the same for the column.

For the latter, click the row, hold CTRL, then click the column.
posted by amicamentis at 9:12 AM on March 28, 2011 [1 favorite]

Response by poster: EndsOfInvention - that's perfect. Now, how do I incorporate all of that into my spreadsheet?
posted by Irontom at 9:56 AM on March 28, 2011

Best answer: First, read the caveats on that link.

Second, in Excel, check if you have the Developer tab available on your ribbon. If not, click File, then Options. Then click Customize Ribbon. One the right side, you should see the Developer tab in the list. Put a checkmark in the box next to it. Click OK.

Now click on the Developer tab. At the far left of the Developer ribbon, click the Visual Basic icon. This opens up the area where you can put code.

In the left side, you should see a tree-like navigation structure, starting with VBA Project at the top, then Microsoft Excel Objects. If necessary, click the plus sign to expand the Microsoft Excel Objects. Double-click on "This Workbook."

On the right side of your window, look for a drop-down that reads "General". Click there and change the drop-down to "Workbook".

To the right of the first drop-down is a second drop-down, that now reads "Open". Click there and select "SheetSelectionChange". This puts two new lines into the white VBA code area.

Switch over to the link provided by EndsOfInvention. Copy everything starting at the line "Dim iColor As Integer" and ending with the line "End With".

Paste all of that into Excel's Visual Basic code area, after the line that starts "Private Sub", and before the line "End Sub".

Click the save icon in the ribbon to save the macro. Click the red X to close VBA.

You may have to save your workbook as a different Excel type. Go to File, then Save As, and choose Excel Macro-Enabled Workbook from the "Save as Type" list. Click Save, and it should start working immediately.

If your macro settings are set to block macros, you have to go to the Developer tab, Macro Security to enable them.
posted by SuperSquirrel at 2:34 PM on March 28, 2011 [1 favorite]