Free Microsoft Excel 2013 Quick Reference

Ability to auto-highlight entire row and column of selected cell

Friends and Experts,

My colleguaes and I work with a rather large data entry/archiving Excel 2007 spreadsheet on a daily basis. It contains several columns and rows that will not easily fit on one viewable screen. We have searched for the ability to highlight the entire data set across the row and down the column of the selected cell. This would allow us to navigate with the directional keys quickly and enter data assuring us we are on the correct row and column with a simple visual indication.

I know that the number and letter of the row and column highlight on their own, but what we are needing is for the entire row and column of data to highlight as well.

I have attached an example of what we would like to see.

Does anyone know of a setting/function/method to accomplish this? I have searched around to no avail.

Post your answer or comment

comments powered by Disqus
Dear All

Whatever cell I select, I want to hightlight the relative row and column of
that cell, how?

Thanks in advance.

Is there anyway to highlight the row and column of the active cell without permanently removing either the conditional formatting in the highlighted row and column or the colour formats? I have the following code:

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 6
Columns(Target.Column).Interior.ColorIndex = 6
End Sub
But this gets rid of all conditional formatting in the active sheet, not ideal. Also, when I delete the code to stop using it the last row and column that was highlighted yellow remains yellow.

I know this might be a bit to ask but can anyone produce VBA code that will full fill my wish of being able to highlight the row and column of the active cell without ruining colour formatting?

Thanks in advance

I know there is a way to highlight and select an entire row or an entire column, for example if you wanted to search just a row or column for something. But is there a setting that highlights the row and column of the cell that you are currently selecting ? Basically I'm looking for a better way to identify which row and column I'm in other than just highlighting the column and row name which is what it currently does by default.

I want excel to shade the row and column of my active cell. As my active
cell changes, so will the shaded row and column.


How do I get the row and position of a cell after I performed a search macro?

The search macro save me time in looking the entire data file but the problem is I need to get the row number and the column number of the cell so that I can continue to perform other functions.

Please assist.

Samuel Ang

I found this code to highlight the active row. I tried to make it highlight the row and column, but I was not successful. What I really need is to highlight the active row and column above and to the left of the active cell, not the entire row and column. For example, if G10 is active, the highlighted cells would be G1:G10 and A10:G10.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel. Range)
Dim i As Long
Cells.Interior.ColorIndex = xlColorIndexNone
If Application. CountA(Target.EntireRow) 0 Then
i = Target.Row
For i = Me.UsedRange.Rows.Count To 1 Step -1
If Application.CountA(Me.Rows(i)) 0 Then
i = i + 1
Exit For
End If
Next i
End If
Rows(i).Interior.ColorIndex = 6
End Sub
Also, I have fill colors on the sheet and I just noticed that the code removes those fill colors. I need it to not remove my fill colors. The only fill colors it should remove are ones it previously colored.

Your help will be greatly appreciated.

-- DJ

I found the code below on It is so close to what I need to do. Dear Juan Pablo, if you read this post, I would love your help in making a little adjustment to the code you have below.

It highlights active row and column, but it overwrites the already colored cells. I need it to highlight active row and column while it keeps the already highlighted cells in their original color. So, say I have some cells in yellow. When the code highlights the active row and column in (say) purple, I would like to be able to see the the whole row and column in purple while the yellow cells remain in yellow.

So can anyone tell me how to make the code below keep the original cell color of the already highlighted cells while it highlights the rest of the cells in the active row and column? Pleasee help?

Thank you!
Option Explicit
'// Amended 14th Feb 2003 - suggestion by Juan Pablo G.
'// International versons may NOT recognise TRUE
'// Suggestion use =1 which evaluates to TRUE,
'// in fact any number that <> 0

Const iInternational As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'// Leave On Error ON for Row offset errors

If iColor < 0 Then
    iColor = 36
    iColor = 36 

End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1


'// Horizontal color banding
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

'// Vertical color banding
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


Quick question: I was wondering if it was possible either via hotkey or some other setting or option so that if I click on a cell the row and column containing that cell are highlighted. I have a lot of information to sift through and I would love if I could click on say A47 and have A be highlighted (temporarily if possible) as well as 47 be highlighted so I can see the info that is in the same column and same row without my eyes losing place.

the only way I've been doing this so far is to actually select the whole row and "fill" it a color then click on the vertical tab to have it highlight and then I get both. But it would be great if there were a more efficient way to do this.



Does anyone know how to auto (Macro) hide rows and columns if cell values are zeroes. I have attached a file of what I'm trying to accomplish. Thanks in advance for your expertise!


Hi, why doesn't this work? I want the entire row and column of the active cell to be selected - now only column "A:A" gets selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Is there a way I can determine if an entire row or column is selected?

So if I select Column A, I can get my macro to recognize that I have done so and do a certain function.

Does anybody know how to display the Row and Column of an Active Cell and
display it in another cell.


I have a dataset in excel with heading in first row and repetitive data in groups next rows.
The first column contains month1 month2 month3 month4 total and total

I want to insert a blank row after last total of each group and column headings after that
so I want to insert a blank row and column headings after last total row of first group and before the row starting with month1 column.

How can I write a macro to insert a blank row and column heading in vba?

Thanks in advance


Can anybody help me to fix this macro i have it to where is highlighting a single cell i will like the macro to highlight entire row base on a single cell value

thankyou for any help provided
Sub colorentirerow()
'base on a value of single cell highlight entire row
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
For Each mycell In Selection
If mycell.Value Like "5" Then
mycell.Interior.ColorIndex = 3
End If
End Sub

i want to select the entire row and entire column on selecting a single cell.
is it possible


Please provide me the code to highlight the active row and column without losing the current formatting.


I am a novice Excel user who needs help with VBA code. I have researched my problem extensively, and have decided to post here as a last resort.

Basically what I need to be able to do is as follows:

If the number in a cell in column E, "risk rating," is within a certain range (total of 5 different ranges) I want the entire row over the range of columns (A-R) to be highlighted a certain color (depending on the range).

I know I can do this with conditional formatting, but only for 3 conditions. I found some code on the internet that will highlight the cell, but not the entire row. Also, this code only works if I type in the numerical value into the cell in column E. It WILL NOT work if the value in column E is the product of the numerical values in columns C and D. Unfortunately, this function must stay.

Therefore, basically I need code that will be able to evaluate the values in cells in column E (which contains the function "=D(row) * C(row)"), and depending on the range that the value in the column E cell falls, highlight the entire row (from column A - R) a certain color. If the cell value in column E is zero, the cell should be left as is (no highlighting).

I will find the colors myself using a macro. The 5 ranges I need are:
1-5, 6-10, 11-15, 16-20, and 21-25.

Please help!!!

Justin Swanson

I have found all the code that will change an entire row or column to a highlighted color, but this doesn't seem to work on a protected sheet if you have cells in the area that is protected.
What I would like to do, therefore, is simply have the adjacent cell in column A highlighted if anywhere in the row is selected.

Is this possible with a protected sheet? And if so, ideas?

I have attached a sheet to demonstrate what I want - if I am working
in cell H13, I would like cell A13 to be highlighted,
in cell H21, I would like cell A21 to be highlighted,
etc. etc.

All gey cells are/ would be protected.

here's a code I'm using to highlight the row and column that intersect a cell I click into .....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect "ana"
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 3
Columns(Target.Column).Interior.ColorIndex = 4
End Sub

However, it's removing any cell-shading that I had on the sheet already, and not returning it.

Is there some addition to the code that will allow me to highlight the intersection of row and column, but NOT kill off any pre-existing cell shading ??

I know it's possible to set these 2 ranges to capture an entire row or column:

Range ("1:1")
Range ("C:C")

How would I do the same using variables if x = 1 and y = C? Please include the declarations as I am having trouble understanding if I can use string for y.

Bonus points, how do I select Range ("A1:A2") entirely with variables?

Thank you!

As I move down a cell, I would like the entire row and column be highlighted. Is that possible?

I would like to be able to view my position easier, and when I am in a column
and row to the far right, having the row and column number highlighted isnt
sufficient. Is there anyway to have the current row and column highlighted
with color?

Dear all,
I am newbie.
Help me write VBA code for insert or delete row and column automatic. Pls see detail attachment file.
I have a report display on a sheet, inclued 2 tables, each table have 6 columns and 1 fixed row, i want to rows of 2 tables change (insert or delete, except to fixed row) with F4 and F5 cell condition.
Thank your very much.

David Lee

File attachment : Download below or click this link

Hello everyone,

I need your help on a project which involves, among others, importing a .csv file which has empty rows and columns.
Here's a code I found on the net and worked on:

     'Imports text file into Excel workbook using ADO.
     'If the number of records exceeds 65536 then it splits it over more than one sheet.
    Dim strFilePath As String, strFilename As String, vFullPath As Variant 
    Dim lngCounter As Long 
    Dim oConn As Object, oRS As Object, oFSObj As Object 
    Dim msg As String 
    Dim style As Integer 
    msg = "Doriti sa importati datele din fisierul .csv?" 
    style = vbQuestion + vbYesNo 
    If MsgBox(msg, style, "Import date") = vbNo Then Exit Sub 
     'Get a text file name
    vFullPath = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv," & _ 
    "Text files (*.txt),*.txt," & _ 
    "All files (*.*), *.*") 
    If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
     'Application.ScreenUpdating = False
     'This gives us a full path name
     'We need to split this into path and file name
    strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path 
    strFilename = oFSObj.GetFile(vFullPath).Name 
     'Open an ADO connection to the folder specified
    Set oConn = CreateObject("ADODB.CONNECTION") 
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strFilePath & ";" & _ 
    "Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1""" 
    Set oRS = CreateObject("ADODB.RECORDSET") 
     'Now actually open the text file and import into Excel
    On Error Goto Eroare 
    oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1 
    While Not oRS.EOF 
        ActiveSheet.Range("$A$1").CopyFromRecordset oRS, 65536 
    MsgBox "Datele au fost importate din fisierul .csv", vbInformation, "Import finalizat" 
    Exit Sub 
    MsgBox "Operatiunea de importare a datelor a esuat." & vbCr & _ 
    "Este posibil ca fisierul sa fie deschis de un alt utilizator." & vbCr & _ 
    "Va rugam sa inchideti fisierul si sa reincercati.", vbCritical, "Import esuat" 
    Exit Sub 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need to do is import the data from the .csv file but I would like to skip the blank rows and columns.
Any help would be very much appreciated.

Thank you.

No luck finding an answer? You could always try Google.