I have a spreadsheet with the following macro in several sheets. It changes the
fill color of the active row to yellow. It's quite handy, as the sheet is a bit
wide so it helps me keep track of what row I'm working with. It has only one
drawback (well, two). When I first open the workbook or first activate another
sheet, the active row is, of course, yellow, but when I move off of that row or
click on another row, the initial row remains yellow. The sheet then has two
rows that are yellow and remains that way unless I scroll through the active
row. That is, make the initial active row active again and then go to another
row.
Also, I can't copy (or cut) and paste from one cell to another on the same
sheet and if I want to copy to another sheet, I first have to pre-select where I
want to copy to.
Here's the code. It's adapted from (I believe) Chip Pearson's website. My
apologies if it's someone else. Any help would be gladly appreciated.

Dim z As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Exit Sub
ActiveCell.EntireRow.Interior.ColorIndex = 6

If z = Empty Then
z = ActiveCell.Row
ElseIf Not z = ActiveCell.Row Then
Rows(z).EntireRow.Interior.ColorIndex = xlColorIndexNone
End If

z = ActiveCell.Row


End Sub

Hi Bill
some inherent darwbacls of using this kind of code :-)
some solution ideas:
1. You may add some code to the workboo_beforeclose event which will
reset the colors in your sheet to normal. This will deal with the
problem that one row remains colored

2. Not muh chance against that as the selection_change event colors the
new row and this clears the clipboard contents

--
Regards
Frank Kabel
Frankfurt, Germany

"Bill Oertell" schrieb im Newsbeitrag
...
I have a spreadsheet with the following macro in several sheets. It
changes the
fill color of the active row to yellow. It's quite handy, as the
sheet is a bit
wide so it helps me keep track of what row I'm working with. It has
only one
drawback (well, two). When I first open the workbook or first
activate another
sheet, the active row is, of course, yellow, but when I move off of
that row or
click on another row, the initial row remains yellow. The sheet then
has two
rows that are yellow and remains that way unless I scroll through the
active
row. That is, make the initial active row active again and then go
to another
row.
Also, I can't copy (or cut) and paste from one cell to another on
the same
sheet and if I want to copy to another sheet, I first have to
pre-select where I
want to copy to.
Here's the code. It's adapted from (I believe) Chip Pearson's
website. My
apologies if it's someone else. Any help would be gladly
appreciated.

Dim z As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Exit Sub
ActiveCell.EntireRow.Interior.ColorIndex = 6

If z = Empty Then
z = ActiveCell.Row
ElseIf Not z = ActiveCell.Row Then
Rows(z).EntireRow.Interior.ColorIndex = xlColorIndexNone
End If

z = ActiveCell.Row


End Sub

I would also add the code Frank mentions in 1 to the Worksheet_DeActivate
event.

You might also want to take a look at Chip Pearson's RowLiner utility
http://www.cpearson.com/excel/RowLiner.htm
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi Bill
some inherent darwbacls of using this kind of code :-)
some solution ideas:
1. You may add some code to the workboo_beforeclose event which will
reset the colors in your sheet to normal. This will deal with the
problem that one row remains colored

2. Not muh chance against that as the selection_change event colors the
new row and this clears the clipboard contents

--
Regards
Frank Kabel
Frankfurt, Germany

"Bill Oertell" schrieb im Newsbeitrag
...
I have a spreadsheet with the following macro in several sheets. It
changes the
fill color of the active row to yellow. It's quite handy, as the
sheet is a bit
wide so it helps me keep track of what row I'm working with. It has
only one
drawback (well, two). When I first open the workbook or first
activate another
sheet, the active row is, of course, yellow, but when I move off of
that row or
click on another row, the initial row remains yellow. The sheet then
has two
rows that are yellow and remains that way unless I scroll through the
active
row. That is, make the initial active row active again and then go
to another
row.
Also, I can't copy (or cut) and paste from one cell to another on
the same
sheet and if I want to copy to another sheet, I first have to
pre-select where I
want to copy to.
Here's the code. It's adapted from (I believe) Chip Pearson's
website. My
apologies if it's someone else. Any help would be gladly
appreciated.

Dim z As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Exit Sub
ActiveCell.EntireRow.Interior.ColorIndex = 6

If z = Empty Then
z = ActiveCell.Row
ElseIf Not z = ActiveCell.Row Then
Rows(z).EntireRow.Interior.ColorIndex = xlColorIndexNone
End If

z = ActiveCell.Row


End Sub

Found this bit of code on a website. Can't remember where, but I can find out
if anyone's interested. The initial row does not remain highlighted. Works
rather nicely. And it has the advantage of not highlighting the row if it's
empty.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" & strRow &
")0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

But I do have one question. How does the the line of code:
..FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" & strRow & ")0"
prevent the sub from highlighting an empty row. I understand the COUNTA
function, but I don't understand how that line prevents the following line from
executing.

Thanks.

That is clever.

It works because it sets up conditional formatting on the row, based upon
whether the row is empty or not. By deleting the condition first then
re-creating it does ensure that the number of conditions on the sheet are
minimised.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
Found this bit of code on a website. Can't remember where, but I can find
out
if anyone's interested. The initial row does not remain highlighted.
Works
rather nicely. And it has the advantage of not highlighting the row if
it's
empty.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" & strRow
&
")0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

But I do have one question. How does the the line of code:
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" & strRow &
")0"
prevent the sub from highlighting an empty row. I understand the COUNTA
function, but I don't understand how that line prevents the following line
from
executing.

Thanks.

I posted a question in this thread about how a certain line in this code works,
and I would appreciate it if everyone would just ignore it. Pretend I didn't
ask it. Thanks.

"Bob Phillips" wrote in message
...
That is clever.

It works because it sets up conditional formatting on the row, based upon
whether the row is empty or not. By deleting the condition first then
re-creating it does ensure that the number of conditions on the sheet are
minimised.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
Found this bit of code on a website. Can't remember where, but I can find
out
if anyone's interested. The initial row does not remain highlighted.
Works
rather nicely. And it has the advantage of not highlighting the row if
it's
empty.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" & strRow
&
")0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

But I do have one question. How does the the line of code:
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" & strRow &
")0"
prevent the sub from highlighting an empty row. I understand the COUNTA
function, but I don't understand how that line prevents the following line
from
executing.

Thanks.

bit late, 17 hours after I answered it<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
I posted a question in this thread about how a certain line in this code
works,
and I would appreciate it if everyone would just ignore it. Pretend I
didn't
ask it. Thanks.

"Bob Phillips" wrote in message
...
That is clever.

It works because it sets up conditional formatting on the row, based
upon
whether the row is empty or not. By deleting the condition first then
re-creating it does ensure that the number of conditions on the sheet
are
minimised.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
Found this bit of code on a website. Can't remember where, but I can
find
out
if anyone's interested. The initial row does not remain highlighted.
Works
rather nicely. And it has the advantage of not highlighting the row
if
it's
empty.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" &
strRow
&
")0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

But I do have one question. How does the the line of code:
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" &
strRow &
")0"
prevent the sub from highlighting an empty row. I understand the
COUNTA
function, but I don't understand how that line prevents the following
line
from
executing.

Thanks.

Yeah. I know. But at least you didn't append, "You idiot" at the end. Thanks.

BTW, I ran into this bit of code on MrExcel.com. It does the job nicely and
doesn't erase my existing conditional format:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Target.Cells.Count = 1 Then
With Rows(Target.Row).Interior
.ColorIndex = 6
'.Pattern = xlSolid
End With
End If
End Sub

I changed it slightly to:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then Exit Sub
Cells.Interior.ColorIndex = xlNone
With Rows(Target.Row).Interior
.ColorIndex = 6
End With
End Sub


"Bob Phillips" wrote in message
...
bit late, 17 hours after I answered it<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
I posted a question in this thread about how a certain line in this code
works,
and I would appreciate it if everyone would just ignore it. Pretend I
didn't
ask it. Thanks.

"Bob Phillips" wrote in message
...
That is clever.

It works because it sets up conditional formatting on the row, based
upon
whether the row is empty or not. By deleting the condition first then
re-creating it does ensure that the number of conditions on the sheet
are
minimised.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
Found this bit of code on a website. Can't remember where, but I can
find
out
if anyone's interested. The initial row does not remain highlighted.
Works
rather nicely. And it has the advantage of not highlighting the row
if
it's
empty.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" &
strRow
&
")0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

But I do have one question. How does the the line of code:
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" &
strRow &
")0"
prevent the sub from highlighting an empty row. I understand the
COUNTA
function, but I don't understand how that line prevents the following
line
from
executing.

Thanks.

Question: how can I change this code to not affect row 1?

"Bill Oertell" wrote in message
...
Yeah. I know. But at least you didn't append, "You idiot" at the end.
Thanks.

BTW, I ran into this bit of code on MrExcel.com. It does the job nicely and
doesn't erase my existing conditional format:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Target.Cells.Count = 1 Then
With Rows(Target.Row).Interior
.ColorIndex = 6
'.Pattern = xlSolid
End With
End If
End Sub

I changed it slightly to:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then Exit Sub
Cells.Interior.ColorIndex = xlNone
With Rows(Target.Row).Interior
.ColorIndex = 6
End With
End Sub


"Bob Phillips" wrote in message
...
bit late, 17 hours after I answered it<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
I posted a question in this thread about how a certain line in this code
works,
and I would appreciate it if everyone would just ignore it. Pretend I
didn't
ask it. Thanks.

"Bob Phillips" wrote in message
...
That is clever.

It works because it sets up conditional formatting on the row, based
upon
whether the row is empty or not. By deleting the condition first then
re-creating it does ensure that the number of conditions on the sheet
are
minimised.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
Found this bit of code on a website. Can't remember where, but I can
find
out
if anyone's interested. The initial row does not remain highlighted.
Works
rather nicely. And it has the advantage of not highlighting the row
if
it's
empty.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete

With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" &
strRow
&
")0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

But I do have one question. How does the the line of code:
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTA(" &
strRow &
")0"
prevent the sub from highlighting an empty row. I understand the
COUNTA
function, but I don't understand how that line prevents the following
line
from
executing.

Thanks.