Line Colour

[Sharid]

Is there a way to set a macro that will highlight the entire ROW in a colour when I select a CELL, so that it is easy for the user to identify the information.

I have a very large worksheet and I am currently using freeze frame, but it still becomes a bit confusing as there is a lot of information on the sheet to look at.

e.g. if I was in column J cell 15 then the whole ROW would change colour to blue

If this is possible, then I would like it to start from Cell 9 and end for Cell 33


Cheers

 

[VoG]

CAUTION: The following will overwrite any manually filled colours and won't override Conditional Formats.

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row < 9 Then
    Rows(9).Interior.ColorIndex = xlNone
    Exit Sub
ElseIf Target.Row > 33 Then
    Rows(33).Interior.ColorIndex = xlNone
    Exit Sub
End If
Rows("9:33").Interior.ColorIndex = xlNone
Target.EntireRow.Interior.ColorIndex = 5
End Sub

then close the code window using the X.

 

[Sharid]

This is what I am looking for apart from the bit that overwrite the manually filled colours, is there a work arround to this.

cheers

 

[VoG]

This is what I am looking for apart from the bit that overwrite the manually filled colours, is there a work arround to this.

cheers

Courtesy of Erik in another thread http://www.mrexcel.com/forum/showthread.php?t=328712

Right click the sheet tab and replace the existing code with

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

then close the code window.

Select rows 9 to 33, Format > Conditional Formatting, select Formula Is and enter the formula

=ROW()=CELL("row")

then click the Format button, on the Fill tab select a blue colour then OK.