Highlight Row and Column of Selected Cell

[JACOBB9900]

Hello,

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

Thank you.

 

[DanteAmor]

Check this:

https://www.mrexcel.com/forum/excel...ell-excel-using-conditional-formatting-2.html

 

[My Aswer Is This]

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

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

 

[Peter_SSs]

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.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("Z1").Value = ActiveCell.Row
  Range("Z2").Value = ActiveCell.Column
End Sub

 

[DanteAmor]

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
End Sub

 

[Peter_SSs]

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")

 

[Peter_SSs]

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.

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

 

[DanteAmor]

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")