Select both row and column together

crave4excel

New Member
Joined
Feb 2, 2012
Messages
45
Hi!
i'm not sure if this is even do-able with Excel, but is there a shortcut to select (or highlight) both the row and column of a specific cell, without manually select each?
To visualize this, this action would result in a highlighted "+" in the spreadsheet so i can easily see the row and column header, without scrolling?

And no, i am not looking to freeze the pane :)

Thank you!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
a few questions.

1. are you using a table, or just a normal set of data in a worksheet?
2. Does each row, starting with A2 as an example, have a set Row Title
3. Sounds like each column has a header, but you do not want to lock that row
4. What output are you looking for? something like Column Header + Row Header?
 
Upvote 0
You could this code in your worksheet event to highlight both Row and Column.....BUT, it will overwrite any conditional formatting on the sheet
VBA Code:
Option Explicit
Const iInternational As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
On Error Resume Next
iColor = Target.Interior.ColorIndex
If iColor < 0 Then
    iColor = 36
Else
    iColor = iColor + 1
End If
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1
Cells.FormatConditions.Delete
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub
 
Upvote 0
An alternate to that, in regular formatting, which I use on a large table with an applied custom table style. Some extra code limits the highlight to the table data area to prevent it destroying the formatting outside of there and that of the header row.

VBA Code:
Public Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) '10.12.2019 chg ver 04.07.2017
Rem section code adapted from https://www.extendoffice.com/documents/excel/1494-excel-highlight-active-row-and-column.html
Static xRow: Static xColumn: Static coleur As Integer '10.12.2019 has to be integer
coleur = 8  'black = 1, white = 2, red = 3, green = 4, blue = 5, yellow = 6, magenta = 7, teal = 8, deepred = 9
If xColumn <> "" Then
    Columns(xColumn).Interior.ColorIndex = xlNone
    Rows(xRow).Interior.ColorIndex = xlNone
End If
   pRow = Selection.Row: pColumn = Selection.Column
   xRow = pRow: xColumn = pColumn
With Columns(pColumn).Interior
If ActiveCell.Column < 30 Then .ColorIndex = coleur: .Pattern = xlSolid
End With
With Rows(pRow).Interior
If ActiveCell.Row > 1 Then .ColorIndex = coleur: .Pattern = xlSolid
End With
End Sub
 
Upvote 0
T
You could this code in your worksheet event to highlight both Row and Column.....BUT, it will overwrite any conditional formatting on the sheet
VBA Code:
Option Explicit
Const iInternational As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
On Error Resume Next
iColor = Target.Interior.ColorIndex
If iColor < 0 Then
    iColor = 36
Else
    iColor = iColor + 1
End If
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1
Cells.FormatConditions.Delete
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

Thanks! I will give this a try
 
Upvote 0
T
An alternate to that, in regular formatting, which I use on a large table with an applied custom table style. Some extra code limits the highlight to the table data area to prevent it destroying the formatting outside of there and that of the header row.

VBA Code:
Public Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) '10.12.2019 chg ver 04.07.2017
Rem section code adapted from https://www.extendoffice.com/documents/excel/1494-excel-highlight-active-row-and-column.html
Static xRow: Static xColumn: Static coleur As Integer '10.12.2019 has to be integer
coleur = 8  'black = 1, white = 2, red = 3, green = 4, blue = 5, yellow = 6, magenta = 7, teal = 8, deepred = 9
If xColumn <> "" Then
    Columns(xColumn).Interior.ColorIndex = xlNone
    Rows(xRow).Interior.ColorIndex = xlNone
End If
   pRow = Selection.Row: pColumn = Selection.Column
   xRow = pRow: xColumn = pColumn
With Columns(pColumn).Interior
If ActiveCell.Column < 30 Then .ColorIndex = coleur: .Pattern = xlSolid
End With
With Rows(pRow).Interior
If ActiveCell.Row > 1 Then .ColorIndex = coleur: .Pattern = xlSolid
End With
End Sub
thx i will try! Was really hoping to avoid the VBA route :(
 
Upvote 0
Hii
a few questions.

1. are you using a table, or just a normal set of data in a worksheet?
2. Does each row, starting with A2 as an example, have a set Row Title
3. Sounds like each column has a header, but you do not want to lock that row
4. What output are you looking for? something like Column Header + Row Header?

Hii im simply trying to select the rows and column of a selected cell so i can easily see the headers without freeze pane.i dont even need them to be highlighted, i just need them to be selected!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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