Worksheet_SelectionChange

[Bill Oertell]

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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[Bill Oertell]

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.

 

[Bob Phillips]

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]

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]

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]

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

 

[Bill Oertell]

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