Highlight Row and Column of Selected Cell

JACOBB9900

New Member
Joined
Apr 8, 2019
Messages
38
Hello,

I need help highlighting the row and column of a cell selected. Any help is appreciated.

Thank you.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  10/24/2019  2:31:52 PM  EDT
If Target.Cells.CountLarge > 1 Then Exit Sub
Cells.Interior.Color = xlNone
Rows(ActiveCell.Row).Interior.Color = vbYellow
Columns(ActiveCell.Column).Interior.Color = vbYellow
End Sub
 
Upvote 0
I prefer the Conditional formatting approach as it will not destroy any existing colour in the worksheet. Another advantage is that if the worksheet already has conditional formatting, this new CF will temporarily over-ride it so the selected row/column is clearly visible. (If other CF is added after the CF described here, it may hinder this & a slight adjustment could be needed. Post back if further information about this is required)

However, I think forcing the sheet to Recalculate on every selection change could be quite onerous on resources if the sheet has a lot of formulas so here is a slight variation on Dante's approach that could be much lighter on resources.

1. Choose a vacant column and hide it. I have used column Z (but it could be any column including columns XFD if you want to get it well out of the road)

2. Select the whole worksheet (by clicking the box at the top left at the intersection of the column labels and row labels). We could select a lesser range but applying it to the whole worksheet doesn't seem to make files too big or slow in my experience.

3. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =$Z$1=ROW() -> Format... -> On the Fill tab select the colour you want for the entire row -> Ok -> Ok

4. With the whole sheet still selected go to Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =$Z$2=COLUMN() -> Format... -> On the Fill tab select the same colour -> Ok -> Ok

5. Right click the sheet name tab and choose 'View Code'

6. Copy and Paste the code below into the main right hand pane that opens at step 5.

7. Close the Visual Basic window & test.

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("Z1").Value = ActiveCell.Row
  Range("Z2").Value = ActiveCell.Column
End Sub
 
Upvote 0
I agree with Peter, I also prefer the conditional format.

In addition to the advantages commented by Peter, with my proposal presented in Post #2 , you have the advantage of continuing to use the Undo function (Control + Z), you lose this function with Peter's macro.


Use the same steps mentioned by Peter, but in steps 3 and 4 put the following formulas:

3.
=ROW()=CELL("row")

4.
=COLUMN()=CELL("column")

In step 6 put the following code:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
End Sub
 
Upvote 0
In addition to the advantages commented by Peter, with my proposal presented in Post #2 , you have the advantage of continuing to use the Undo function (Control + Z),
That's a worthwhile preservation. :)



4.
=COLUMN()=CELL("column")
It may be different in different language versions of Excel but for me that needs to be

=COLUMN()=CELL("col")
 
Upvote 0
BTW, here is another way that does not require Conditional Formatting and also does not interfere with any existing colour (either direct or via CF) in your sheet - provided the RGB colour chosen does not already exist in your sheet - & that is pretty easy to ensure. You do lose the Undo though.

If you already have a lot of colour in the sheet, this method may not be optimal as it does not over-ride any existing colour. This means that if you select in the middle of an already coloured range, the row/column highlighting done by this code may not be visible at all or at least not near your active cell. Still, it can be very useful in many circumstances.

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error Resume Next
    With Application
        .FindFormat.Clear
        .ReplaceFormat.Clear
        .FindFormat.Interior.Color = RGB(184, 214, 204) '<- Choose a colour not already on your sheet
        .ReplaceFormat.Interior.Color = xlNone
        Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
        .FindFormat.Clear
        .ReplaceFormat.Clear
        .FindFormat.Interior.Color = xlNone
        .ReplaceFormat.Interior.Color = RGB(184, 214, 204)  '<- Use the same RGB as above
        Intersect(ActiveCell.EntireRow, ActiveSheet.UsedRange).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
        Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
        .FindFormat.Clear
        .ReplaceFormat.Clear
    End With
    On Error GoTo 0
End Sub
 
Last edited:
Upvote 0
It may be different in different language versions of Excel but for me that needs to be

=COLUMN()=CELL("col")

Thanks Peter again for language correction. You're right it must be "col"

4.
=COLUMN()=CELL("col")
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top