Colour active row but allow copy / paste

[michael.beckinsale]

Hi All,

I have trawled the newsgroups for code to highlight the active row.
Below is the code that l am currently using which works fine until you
want to carry out copy / paste on the active worksheet.

Does anybody have the code that colours the active row AND allows copy
/ paste on the active worksheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static OldRange As Range
Target.EntireRow.Interior.ColorIndex = 36

If Not OldRange Is Nothing Then
OldRange.EntireRow.Interior.ColorIndex = xlNone
End If

Set OldRange = Target

End Sub

All suggestions gratefully received.

Regards

Michael Beckinsale

 

[bigwheel]

Have a look at the following:

http://www.cpearson.com/excel/RowLiner.htm

Not exactly what you want but very useful.

 

[Peter T]

Hi Michael,

Unfortunately changing cells in the event cancels cut/copy. Perhaps disable
changes if in cut or copy mode -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldRange As Range

If Application.CutCopyMode = 0 Then

If Not OldRange Is Nothing Then
OldRange.EntireRow.Interior.ColorIndex = xlNone
End If

Set OldRange = Target(1).EntireRow
OldRange.Interior.ColorIndex = 36
Else
If OldRange Is Nothing Then
Set OldRange = Target(1).EntireRow
Else
Set OldRange = Union(OldRange, Target(1).EntireRow)
End If
End If

End Sub

Apart from disabling I've made some other changes to your routine which you
may or may not prefer - previously selecting another cell in same row left
the row un-coloured (another way is first remove format from all rows in the
used range and not bother with "OldRange").

This is far from satisfactory but might be enough for your needs. I wouldn't
use this myself! (though I do use something very vaguely along the lines of
Chip Pearson's RowLiner as referred to you by "bigwheel").

Regards,
Peter T

 

[michael.beckinsale]

Peter,

Many thanks for the reply. I was thinking of "messing about" with the
code along the lines that you have provided.

I will code a try and let you know how l get on

Regards

Michael