Highlight a specific range from a row when a cell in that range is selected

[aurora446]

Hi, I am new so forgive any mistakes.

I have a table from column B to column M and row 8 to row 47.

What I would like is to highlight the row in that range if i selected a cell in that row.

So for example if I select M8, then cells B8:M8 would be highlighted, but if I was to select N8 or A1, nothing would be highlighted. However I have conditional formatting on one of the columns which I do not want to be overwritten by the highlighting.

Any help would be appreciated.

 

[Michael M]

Any code that will highlight entire rows OR a continuous Range of cells in a row WILL overwrite any conditional formatting.

 

[Snakehips]

aurora446,

Welcome to MrExcel.

You can achieve what you are wanting as the interior colour of a cell and it's conditional colours if they exist, are independent of each other.

So try this code in your sheet's code module.
Right click the sheet tab >> View Code >> and paste into the white space of the editor pane.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B8:M47")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Range("B8:M47").Interior.ColorIndex = -4142
Range("B" & Target.Row & ":M" & Target.Row).Interior.ColorIndex = 3  'edit   3  to give colour of choice
End Sub

Hope that helps.

 

[aurora446]

aurora446,

Welcome to MrExcel.

You can achieve what you are wanting as the interior colour of a cell and it's conditional colours if they exist, are independent of each other.

So try this code in your sheet's code module.
Right click the sheet tab >> View Code >> and paste into the white space of the editor pane.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B8:M47")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Range("B8:M47").Interior.ColorIndex = -4142
Range("B" & Target.Row & ":M" & Target.Row).Interior.ColorIndex = 3  'edit   3  to give colour of choice
End Sub

Hope that helps.

This is fantastic, thanks so much. It seems to change the whole table to a lighter colour fill though. Is there anyway to leave the rest of the table unchanged and just highlight the relevant row? Also is it possible that no row be highlighted if I select a cell outside of this range? Thanks so much again

 

[Snakehips]

This is fantastic, thanks so much. It seems to change the whole table to a lighter colour fill though. Is there anyway to leave the rest of the table unchanged and just highlight the relevant row? Also is it possible that no row be highlighted if I select a cell outside of this range? Thanks so much again

What is the 'normal' colour of the table?
Select a 'normal' cell in the table, open the vb editor and if you cannot see the 'Immediate' Pane at the bottom then click View > Immediate Window.

Type ?ActiveCell.Interior.ColorIndex and hit return. Then the colour index will appear below.

 

[aurora446]

What is the 'normal' colour of the table?
Select a 'normal' cell in the table, open the vb editor and if you cannot see the 'Immediate' Pane at the bottom then click View > Immediate Window.

Type ?ActiveCell.Interior.ColorIndex and hit return. Then the colour index will appear below.

One column is 35 (b), the rest are 20 (c-m)

 

[Snakehips]

Color index 35 renders light blue for me, exactly the same as color index 20.

So, if for you all your table cells are the same then use the following...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B8:M47").Interior.ColorIndex = 35
If Intersect(Target, Range("B8:M47")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Range("B" & Target.Row & ":M" & Target.Row).Interior.ColorIndex = 3
End Sub

If for you, column B renders a different colour to C:M then use......

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B8:B47").Interior.ColorIndex = 35
Range("C8:M47").Interior.ColorIndex = 20
If Intersect(Target, Range("B8:M47")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Range("B" & Target.Row & ":M" & Target.Row).Interior.ColorIndex = 3
End Sub

Hope that helps.

 

[ijdasan]

As a workaround, define a name for the range and set the hyperlink accordingly..

 

[aurora446]

Thanks a lot, this worked great. Very impressed with the speed of the reply too

 

[Snakehips]

Thanks a lot, this worked great. Very impressed with the speed of the reply too

You are welcome.