ExcelBanter - Highlight rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Highlight rows (https://www.excelbanter.com/excel-worksheet-functions/168484-highlight-rows.html)

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?

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---

--
RyGuy

"Scott R" wrote:

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?

Hey Ryan,

That worked great!

Thanx!!

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

"ryguy7272" wrote:

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---

--
RyGuy

"Scott R" wrote:

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?

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

On Tue, 4 Dec 2007 14:41:01 -0800, Scott R
wrote:

Hey Ryan,

That worked great!

Thanx!!

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

"ryguy7272" wrote:

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---

--
RyGuy

"Scott R" wrote:

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?

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" wrote in message
...
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?

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

Ta :)

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?