"Highlight" Entire Row while entering data

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(xlEdgeBottom).LineStyle = xlNone
Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
newrow = ActiveCell.Row
End Sub

'=========

hth,

tim

"lightspeed" <[email protected]> wrote in message
news:[email protected]...
> 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(xlEdgeBottom).LineStyle = xlNone
> Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
> newrow = ActiveCell.Row
> End Sub
>
> '=========
>
> hth,
>
> tim
>
> "lightspeed" <[email protected]> wrote in message
> news:[email protected]...
> > 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(xlEdgeBottom).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" <[email protected]> wrote in message
news:[email protected]...
> 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(xlEdgeBottom).LineStyle =
>> xlNone
>> Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
>> newrow = ActiveCell.Row
>> End Sub
>>
>> '=========
>>
>> hth,
>>
>> tim
>>
>> "lightspeed" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 **** 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(xlEdgeBottom).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(xlEdgeBottom).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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(xlEdgeBottom).LineStyle =
> >> xlNone
> >> Target.EntireRow.Borders(xlBottom).LineStyle = xlContinuous
> >> newrow = ActiveCell.Row
> >> End Sub
> >>
> >> '=========
> >>
> >> hth,
> >>
> >> tim
> >>
> >> "lightspeed" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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?
> >> >> >
> >> >> >
> >> >
> >