Highlight Row and Column of the Selected Cell - 2 Easy Methods

When working on a large list with column headers and row headers, you can improve the legibility of your data by highlighting the row and column of the selected cell, if you click on another cell, a different row and column are highlighted. But if you click outside the list nothing is highlighted.

In this tutorial I show you 2 methods for doing this either with Conditional Formatting or with a simple VBA code. Let me know in a comment which method you prefer.
So, let’s dive in.

Note:
When using the VBA method, the ro color and Column color extends beyond the range of data. I didn't want to deal with this situation to avoid a much more complicated code. If you are interested in solving this issue, then use this code instead replace the words greater than with the greater than symbol:
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Cells.Interior.Color = xlColorIndexNone
Dim Nabil As Range
Dim singlecell As Range
Set Nabil = Range ("A2:E25")
If Target.Row greater than26 Or Target.Column Greater than 5 Then
Cells.Interior.ColorIndex = xlColorIndexNone
Exit Sub
Else
For Each singlecell In Nabil
If singlecell.Row = ActiveCell.Row Or singlecell.Column = ActiveCell.Column Then
singlecell.Interior.ColorIndex = 6
End If
Next singlecell
End If
End Sub

[axion]

The master speaks. Those who seek wisdom listen.

[nadermounir]

I find a huge benefit from this tutorial. your conditional formatting solution is far better than VBA because it doesn't highlight the entire column but rather the highlight the row and column within the range only. the tutorial is amazing and your Conditional formatting solution is unique. Thanks for this great tutorial and for showing us VBA. 👍

[ierrtrd]

This video along with the additional comments allowed me to come up with a working solution that highlights only rows in a list range not starting in Column A and Row and also one that will be extended. Excellent thanks.

[deepakmirchandani]

thanks sir for clearly and slowly explaining these exclusive methods. thanks a lot.

[redhaakhund]

Thank you so much and really smart solutions. As always, your videos and explanation are excellent. All the best and looking forward to more videos.👍👍👍👍👍

[mswordexpert]

thanks a lot.
regarding to your question... you can revile the number of the columns by enabling this option: file > Options> Formulas > R1C1 reference style

[vivekphadke]

Simple and very useful, even a person not trained in VBA can create such file and use it (thanks to your video and commentary) I prefer VBA for long term solution.

[Al-Ahdal]

1st comment, I prefer conditional formatting.

[ivaniffah]

Sir I want to highlight columns and rows for corresponding selected multiple cells using conditional formatting. Please can you help?

[ivaniffah]

HELLO, DO YOU KNOW HOW TO USE CONDITIONAL FORMATING TO HIGHLIGH ROWS AND COLUMNS WHEN MULTIPLE CELLS ARE SELECTED. SUCH AS WHEN SELECTED FROM A3 TO D5 WITHIN A RANGE THE COLUMN FROM A TO D AND ROW FROM 3 TO 5 ARE HIGHLIGHTED. THIS NEEDS TO BE DYNAMIC TOO.
THANKS IN ADVANCE.

[vadodarajilla]

This works, but the biggest problem is that copy and paste function does not work after putting this in place

[IvanCortinas_ES]

Last row with data =MAX((A:A<>"")*ROW((A:A)))
Last column with data =MAX((1:1<>"")*COLUMN((1:1)))
(Assuming an homogeneous data range like the one in the example)

[martyc]

Great Video- I prefer cond formatting as it works on the web, but there’s still that one line of VBA, would you know if there’s script to overcome this?

[dannyhsu]

I need help. Somehow when I use the conditional formatting, it doesn't highlight the rows or columns.

[yosefk]

Thanks for sharing this. How would you in the vba example adapt the code so that the highlighting of rows and columns won't go beyond the used rows and columns?

[Rkeev]

If you add a volatile function there is no need for vba or calculate, correct? Like placing &t(now()) at the end of ur cond format formula

[msamamah]

This is great,
But I need to know how to make it when I search in a data in the table, then the required Data with the row return at the first row. Thank you in advance

[michaelt]

Using conditional formatting, is there a way to make the selected cell one color and the row and column another color? As an example, bright yellow for the selected cell and a less vibrant shade for the row and column.

[onurtunc]

thanks for instruction. i m looking for more efficient way to use in huge data lists. in your example code works fast but huge list this code works slow. i researched so many topics for fastest way unfortunatelly i couldnt find. microsoft may add this highlight function in excel.
this is the code for calculation:
Private Sub Target As Range)
If Target.Row < Then
Application.Calculate
End If
End Sub

[donaldpurdy]

I listened watched the entire video. I copied the formula exact ... to include what was upper case and what was lower case. I entered the formula exactly as written in 'Conditional Formatting' under New Rule and Every time I hit the second OK after selecting the Fill color .... The formula is not accepted. This does not work in the Excel version (MS 365) I am using.