Highlighting a row depending on which cell i am clicked on?

[chris_streeter]

Hi,

Can anyone help me highlight a row depending on what cell I have clicked on?

For example if I am in cell A1 i would like the entire row highlighted, if i then press enter to move to cell A2 i would like the first row to un-highlight and for row 2 to highlight instead?

I think this would be useful in a number of spreadsheets i use.

Cheers

 

[ace19852]

Hi I didn't know how to do this but a quick search on the net and there is a great utube vid explaining how to do this using conditional formatting. See link and if you like it use it.

Excel 2007: Highlight Active Row - YouTube

Cheers

 

[ace19852]

Also if you want a VBA solution this is the best I found

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click ThisWorkbook

3. In the Window paste the following

Note Column is remarked out so isn't working at the moment as you only requested it to work for rows. If you want it to work for Columns as well remove the ' at the being of the line. If you change the ColorIndex number you get a different color. ColorIndex list try this link Excel Color Palette and Color Index change using VBA | Excel & VBA – Databison

'.EntireColumn.Interior.ColorIndex = 8


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
With Target
' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.ColorIndex = 8
'.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
End Sub

Cheers