Highlight row from the active cell

[mclambchop]

Hello, I would like to highlight an entire row in a different colour if the use selects any cell within that row. I have cobbled together the code below which works but it doesnt select the entire row (where there is data, not the entire row) and it changes the colour of the entire column. Could someone help me change the code?


'Range(Cells(5, 10), Cells(5051, 8)).Interior.ColorIndex = xlNone
'If Target.Row >= 5 And Target.Row <= 5051 Then
' Cells(Target.Row, 8).Interior.ColorIndex = 1
'End If

'Range(Cells(5, 10), Cells(5051, 8)).Font.ColorIndex = 1
'If Target.Row >= 5 And Target.Row <= 5051 Then
' Cells(Target.Row, 8).Font.ColorIndex = 2
'End If

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is any use.

1. Select all the columns (entire columns) that you want to highlight rows in.

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** -> Ok -> Ok

3. Right click the sheet name tab and choose 'View Code'

4. Copy and Paste the code below into the main right hand pane that opens at step 3.

5. Close the Visual Basic window & test.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

An advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

 

[mclambchop]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is any use.

1. Select all the columns (entire columns) that you want to highlight rows in.

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** -> Ok -> Ok

3. Right click the sheet name tab and choose 'View Code'

4. Copy and Paste the code below into the main right hand pane that opens at step 3.

5. Close the Visual Basic window & test.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

An advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

Thank you so much, worked a treat! I have updated my profile, thanks for the advice, appreciated

 

[Peter_SSs]

Thank you so much, worked a treat!

You're welcome. Thanks for the follow-up.

I have updated my profile,

Great, thanks for doing that.