Highlight rows

[Scott R]

Hi Guys,Im pretty sure this cant be done but thought id check with the
brainstrust..
I have XL2003 and would like the entire row to be highlighted when im in a
particular cell so it is easy to reference data in other cells on that row. I
know i could click on the row number itself but is there a way for it to
happen by just being in a particular cell?

 

[ryguy7272]

I think this will do what you want:
Right-click the worksheet tab, and paste this code in the window that opens...

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
With .Borders(xlBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
..Interior.ColorIndex = 20
End With
End With
With .EntireColumn
..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
With .Borders(xlRight)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
..Interior.ColorIndex = 20
End With
End With

..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub


I forgot where I got this, but I think it was from this discussion group a
while back.


Regards,
Ryan---

 

[Scott R]

Hey Ryan,

That worked great!

Thanx!!

( I did have a browse in prev posts but couldnt find anything)

 

[Gord Dibben]

Scott

Note this code will wipe out any Conditional Formatting you may have on cells.

Also highlights multiple selections.

Might be better of to go with Chip Pearson's RowLiner add-in that does not clear
CF's

http://www.cpearson.com/excel/RowLiner.htm


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Are you looking for this effect? Assuming the cell you are interested in
reacting to is C4...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("C4").Address Then
Target.EntireRow.Select
Target.Activate
End If
End Sub

There are other ways to frame the test depending on exactly what criteria
you want to use.

Rick

 

[Scott R]

Thanks for those, guys. Ill have a look and see which one is going to work
best.

Ta