I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I key
in data on the right side of the screen, it's difficult to distinguish
if i'm on the correct row. The spreadsheet has already used up all the
contitional formatting, so coloring even/odd rows won't help me either.


I would love something that puts a hard underline under the entire row
when any cell of that row is selected, and then removes the line when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there is
no need to recall the pre-selected status.

any ideas?

The following VBA event code will put a continuous bottom border on the
active row only, using conditional formatting. Copy & paste it into the code
module of the sheet where you want it to happen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
End With
End Sub

Please note: all conditional formatting on the sheet is removed every time
a different cell is selected.

Hope this helps,

Hutch

"lightspeed" wrote:

I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I key
in data on the right side of the screen, it's difficult to distinguish
if i'm on the correct row. The spreadsheet has already used up all the
contitional formatting, so coloring even/odd rows won't help me either.


I would love something that puts a hard underline under the entire row
when any cell of that row is selected, and then removes the line when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there is
no need to recall the pre-selected status.

any ideas?

THanks for the nifty bit of code It almost does what I want.
Unfortunately, as I said, the conditional formatting on my spreadsheet
is "used up" meaning just about every cell has the maximum (3) formats
already (cells are colored according to their value). I don't want to
loose this formatting when selecting a cell. I am willing to loose
any borders.


Tom Hutchins wrote:
The following VBA event code will put a continuous bottom border on the
active row only, using conditional formatting. Copy & paste it into the code
module of the sheet where you want it to happen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
End With
End Sub

Please note: all conditional formatting on the sheet is removed every time
a different cell is selected.

Hope this helps,

Hutch

"lightspeed" wrote:

I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I key
in data on the right side of the screen, it's difficult to distinguish
if i'm on the correct row. The spreadsheet has already used up all the
contitional formatting, so coloring even/odd rows won't help me either.


I would love something that puts a hard underline under the entire row
when any cell of that row is selected, and then removes the line when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there is
no need to recall the pre-selected status.

any ideas?

lightspeed,

i started working on a solution, then remembered i had my own work to do!

anyway, here's a possible solution (richt-click on the sheet1 tab, choose
view code and insert this whole bit of code): -

'=========

Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oldrow
On Error Resume Next
oldrow = newrow
Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeBo ttom).LineStyle = xlNone
Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
newrow = ActiveCell.Row
End Sub

'=========

hth,

tim

"lightspeed" wrote in message
ups.com...
THanks for the nifty bit of code It almost does what I want.
Unfortunately, as I said, the conditional formatting on my spreadsheet
is "used up" meaning just about every cell has the maximum (3) formats
already (cells are colored according to their value). I don't want to
loose this formatting when selecting a cell. I am willing to loose
any borders.


Tom Hutchins wrote:
The following VBA event code will put a continuous bottom border on the
active row only, using conditional formatting. Copy & paste it into the
code
module of the sheet where you want it to happen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
End With
End Sub

Please note: all conditional formatting on the sheet is removed every
time
a different cell is selected.

Hope this helps,

Hutch

"lightspeed" wrote:

I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I key
in data on the right side of the screen, it's difficult to distinguish
if i'm on the correct row. The spreadsheet has already used up all the
contitional formatting, so coloring even/odd rows won't help me either.


I would love something that puts a hard underline under the entire row
when any cell of that row is selected, and then removes the line when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there is
no need to recall the pre-selected status.

any ideas?

Tim,

I too face a same difficulty as lightspedd faces. In my case, I have
all my cells with borders. So, the code doesnt highlight as much as it
would do for cells without bordering. Can we widen the width of the
line of the entire row when one cell is clicked and also make it to
disappear (i.e. retun to the normal border that I have) when the cell
is unselected?

Thanks,
Thulasiram

Tim Marsh wrote:
lightspeed,

i started working on a solution, then remembered i had my own work to do!

anyway, here's a possible solution (richt-click on the sheet1 tab, choose
view code and insert this whole bit of code): -

'=========

Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oldrow
On Error Resume Next
oldrow = newrow
Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeBo ttom).LineStyle = xlNone
Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
newrow = ActiveCell.Row
End Sub

'=========

hth,

tim

"lightspeed" wrote in message
ups.com...
THanks for the nifty bit of code It almost does what I want.
Unfortunately, as I said, the conditional formatting on my spreadsheet
is "used up" meaning just about every cell has the maximum (3) formats
already (cells are colored according to their value). I don't want to
loose this formatting when selecting a cell. I am willing to loose
any borders.


Tom Hutchins wrote:
The following VBA event code will put a continuous bottom border on the
active row only, using conditional formatting. Copy & paste it into the
code
module of the sheet where you want it to happen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
End With
End Sub

Please note: all conditional formatting on the sheet is removed every
time
a different cell is selected.

Hope this helps,

Hutch

"lightspeed" wrote:

I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I key
in data on the right side of the screen, it's difficult to distinguish
if i'm on the correct row. The spreadsheet has already used up all the
contitional formatting, so coloring even/odd rows won't help me either.


I would love something that puts a hard underline under the entire row
when any cell of that row is selected, and then removes the line when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there is
no need to recall the pre-selected status.

any ideas?

Thulasiram,

try this code instead...

'=============
Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oldrow
On Error Resume Next
oldrow = newrow
'Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeB ottom).LineStyle = xlNone
With Worksheets("Sheet1").Rows(oldrow).Borders(xlBottom )
..Color = RGB(0, 0, 0)
..Weight = xlThin
End With
'Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
With Target.EntireRow.Borders(xlBottom)
..Color = RGB(255, 0, 0)
..Weight = xlThick
End With
newrow = ActiveCell.Row
End Sub
'====================



"Thulasiram" wrote in message
oups.com...
Tim,

I too face a same difficulty as lightspedd faces. In my case, I have
all my cells with borders. So, the code doesnt highlight as much as it
would do for cells without bordering. Can we widen the width of the
line of the entire row when one cell is clicked and also make it to
disappear (i.e. retun to the normal border that I have) when the cell
is unselected?

Thanks,
Thulasiram

Tim Marsh wrote:
lightspeed,

i started working on a solution, then remembered i had my own work to do!

anyway, here's a possible solution (richt-click on the sheet1 tab, choose
view code and insert this whole bit of code): -

'=========

Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oldrow
On Error Resume Next
oldrow = newrow
Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeBo ttom).LineStyle =
xlNone
Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
newrow = ActiveCell.Row
End Sub

'=========

hth,

tim

"lightspeed" wrote in message
ups.com...
THanks for the nifty bit of code It almost does what I want.
Unfortunately, as I said, the conditional formatting on my spreadsheet
is "used up" meaning just about every cell has the maximum (3) formats
already (cells are colored according to their value). I don't want to
loose this formatting when selecting a cell. I am willing to loose
any borders.


Tom Hutchins wrote:
The following VBA event code will put a continuous bottom border on
the
active row only, using conditional formatting. Copy & paste it into
the
code
module of the sheet where you want it to happen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Borders(xlBottom).LineStyle =
xlContinuous
End With
End Sub

Please note: all conditional formatting on the sheet is removed every
time
a different cell is selected.

Hope this helps,

Hutch

"lightspeed" wrote:

I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I
key
in data on the right side of the screen, it's difficult to
distinguish
if i'm on the correct row. The spreadsheet has already used up all
the
contitional formatting, so coloring even/odd rows won't help me
either.


I would love something that puts a hard underline under the entire
row
when any cell of that row is selected, and then removes the line
when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there
is
no need to recall the pre-selected status.

any ideas?

You nailed it!!!. Thank you all for your help. I wish excel would
provide faint shading on selected row and column by default, but this
is the next best thing. If you'll permit me to be very anal now: the
tool can be befuddled a little if multiple rows are selected at once.
It only unformats the last selected row, leaving some extra underlines
behind.

In any case, I've excluded the header row from loosing its hard
underline:

Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection.Row = 1 Then GoTo endnow
Dim oldrow
On Error Resume Next
oldrow = newrow
Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeBo ttom).LineStyle =
xlNone
With Target.EntireRow.Borders(xlBottom)
..Color = RGB(0, 0, 255)
..Weight = xlThick
End With
newrow = ActiveCell.Row
endnow:
End Sub
'====================



Tim Marsh wrote:
Thulasiram,

try this code instead...

'=============
Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oldrow
On Error Resume Next
oldrow = newrow
'Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeB ottom).LineStyle = xlNone
With Worksheets("Sheet1").Rows(oldrow).Borders(xlBottom )
.Color = RGB(0, 0, 0)
.Weight = xlThin
End With
'Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
With Target.EntireRow.Borders(xlBottom)
.Color = RGB(255, 0, 0)
.Weight = xlThick
End With
newrow = ActiveCell.Row
End Sub
'====================



"Thulasiram" wrote in message
oups.com...
Tim,

I too face a same difficulty as lightspedd faces. In my case, I have
all my cells with borders. So, the code doesnt highlight as much as it
would do for cells without bordering. Can we widen the width of the
line of the entire row when one cell is clicked and also make it to
disappear (i.e. retun to the normal border that I have) when the cell
is unselected?

Thanks,
Thulasiram

Tim Marsh wrote:
lightspeed,

i started working on a solution, then remembered i had my own work to do!

anyway, here's a possible solution (richt-click on the sheet1 tab, choose
view code and insert this whole bit of code): -

'=========

Option Explicit
Public newrow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oldrow
On Error Resume Next
oldrow = newrow
Worksheets("Sheet1").Rows(oldrow).Borders(xlEdgeBo ttom).LineStyle =
xlNone
Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
newrow = ActiveCell.Row
End Sub

'=========

hth,

tim

"lightspeed" wrote in message
ups.com...
THanks for the nifty bit of code It almost does what I want.
Unfortunately, as I said, the conditional formatting on my spreadsheet
is "used up" meaning just about every cell has the maximum (3) formats
already (cells are colored according to their value). I don't want to
loose this formatting when selecting a cell. I am willing to loose
any borders.


Tom Hutchins wrote:
The following VBA event code will put a continuous bottom border on
the
active row only, using conditional formatting. Copy & paste it into
the
code
module of the sheet where you want it to happen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Borders(xlBottom).LineStyle =
xlContinuous
End With
End Sub

Please note: all conditional formatting on the sheet is removed every
time
a different cell is selected.

Hope this helps,

Hutch

"lightspeed" wrote:

I enter spreadsheet data across extremely long rows. I have frozen
important reference data from column A. The trouble is that when I
key
in data on the right side of the screen, it's difficult to
distinguish
if i'm on the correct row. The spreadsheet has already used up all
the
contitional formatting, so coloring even/odd rows won't help me
either.


I would love something that puts a hard underline under the entire
row
when any cell of that row is selected, and then removes the line
when
the cell is unselected. --like using a ruler to read a hardcopy. I
don't use the hard underline anywhere else in the document, so there
is
no need to recall the pre-selected status.

any ideas?