Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Colour active row but allow copy / paste

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
michael.beckinsale@noms.gov.uk - 19 Jan 2006 12:42 GMT
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 - 19 Jan 2006 13:58 GMT
Have a look at the following:

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

Not exactly what you want but very useful.

> Hi All,
>
[quoted text clipped - 23 lines]
>
> Michael Beckinsale
Peter T - 19 Jan 2006 16:22 GMT
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

> Hi All,
>
[quoted text clipped - 23 lines]
>
> Michael Beckinsale
michael.beckinsale@ntlworld.com - 20 Jan 2006 10:52 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.