Highlight Entire Row & Column

Dear Experts,

I am working on a huge excel workbook and I need that if select any cell for entry or edit entire row and column should highlight so that I can't make wrong entry.
Like if I am working in Cell AN302, so AN column and 302 row should highlight.

Please guide me on this issue.


Thanks in advance.

Right Click on your Sheet Tab --> View Code --> Paste the code given below in the opened code window --> Close the VBA Editor --> Save your workbook as Excel Macro-Enabled Workbook --> It's done.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = 0
    Cells.Borders.LineStyle = xlNone
    Union(Target.EntireRow, Target.EntireColumn).Interior.ColorIndex = 6
    Target.EntireColumn.BorderAround xlContinuous, xlThin
    Target.EntireRow.Cells.BorderAround xlContinuous, xlThin
End Sub

Does this help?

Hi SKTNEER,
Its working as per my requirement.

Only some issues that is it possible that: -
1) My format as border should not remove
2) It will highlight only Working area (not upto end)

If it can be please do.

Thanks alot.

Change the earlier code to this......

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells.Interior.ColorIndex = 0
    Union(Range(Cells(Target.Row, 1), Cells(Target.Row, lc)), Range(Cells(1, Target.Column), Cells(lr, Target.Column))).Interior.ColorIndex = 6
End Sub