Active cell highlight

[Guest]

Hi,

I would like to know if there was a way to have an active cell always in
yellow or any color.

Example: If the active cell is moved around, the active cell will be always
in yellow.

Thank you in advance

 

[Dave Peterson]

maybe....

you may want to try Chip Pearson's Rowliner:
http://www.cpearson.com/excel/RowLiner.htm

 

[Gord Dibben]

E

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub

This is worksheet event code.

Right-click on the sheet tab and "View Code"

Copy and paste the code above into that module.

Alternative..............

Go to Chip Pearson's site and downloas his Rowliner Add-in so's you can have
the highlighting available for all open workbooks and sheets.

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


Gord Dibben Excel MVP

 

[Guest]

Hi Gord............

This code works neat, except I seem to have lost the ability to
CopyAndPaste..........what might I have done wrong?

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Nothing. Lots of code in macros can destroy the clipboard. It's a choice you
have to make--keep the clipboard and lose the formatting or keep the formatting
and lose the clipboard.

 

[Gord Dibben]

Chuck

You've done nothing wrong.

I have never been able to get the copy/paste function back using this code.

Tried wrapping in eneableevents true and false, but no joy.

Chip's rowliner add-in makes provision for this by highlighting only a portion
of the activecell so's you can right-click or double-click on the
unhighlighted portion for copy/paste etc.

Best to go with that unless someone will jump in with a fix for the code I
posted(which was from Chip long time past).


Gord

 

[Sandy Mann]

Gord,

Oddly enough it the paste functionality remains for me in XL95 if I copy
from the formula bar. Also it seems to work just as well if I comment out
the IF and End If lines and just leave the:

OldCell.Interior.ColorIndex = xlColorIndexNone

By itself

--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Gord Dibben]

Thanks Sandy

Yes I can still copy/paste from the formula bar but not multiple ranges.

If I rem out those lines in 2002 or 2003 it crashes looking for an object
variable.


Gord

 

[Sandy Mann]

from the formula bar. Also it seems to work just as well if I comment out
the IF and End If lines and just leave the:

OldCell.Interior.ColorIndex = xlColorIndexNone

Ah! there is a reason for those lines! I tried recording a Macro of copying
from the Formula Bar and got only:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/12/2005 by Sandy Mann
'

'
Range("H8").Select
ActiveSheet.Paste
End Su

If I then select a cell and copy it, (not from the Formula Bar this time,)
and run the Macro it fails with

Error 1004 "Paste method of worksheet class failed" and highlights the
"ActiveSheet.Paste" line in Macro1, presumably because there is nothing to
paste.

OK all very logical but now if I Debug and End to exit from VBA, every time
I select a new cell I get a Runtime error 91 "Object variable of worksheet
class failed" and it highlights the "OldCell.Interior.ColorIndex =
xlColorIndexNone" line in the Event Macro and continues to do so until I
remove apostrophes from the commented out lines.

mmmmm...... there's more to this VBA than meets the eye <g>

 

[Gord Dibben]

Muuuuuuuuuuuuuuuuuuch more<g>

And mine eyes are getting dimmer as the years pass.


Gord

 

[Guest]

Thank you, this is very helpful

E

maybe....

you may want to try Chip Pearson's Rowliner:
http://www.cpearson.com/excel/RowLiner.htm

 

[Guest]

Thanks all for helping me on this subject.

Have a nice day

E

 

[Guest]

Hi,

This was really useful for somethign I am working on - but is it possible to
return the cell to it's orginal colour once it is no longer the active cell
(and therefore not highlighted). At the moment it returns the cell to white.

Thanks

 

[Guest]

Hi Dave

I have the following copied into the VB and safe it as .xla.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastChange
Application.ScreenUpdating = False
If LastChange = Empty Then
LastChange = ActiveCell.Address
End If
Range(LastChange).EntireColumn.Interior.ColorIndex = xlNone
Range(LastChange).EntireRow.Interior.ColorIndex = xlNone
ActiveCell.EntireColumn.Interior.ColorIndex = 15
ActiveCell.EntireRow.Interior.ColorIndex = 15
LastChange = ActiveCell.Address
Application.ScreenUpdating = True
End Sub

It looks perfect on the existing Excel.

I close all the Excel, and open a new Excel spreadsheet. Then, I loss this
function. I check in VB, it is there. I check in Add-Ins, it is already
checked/ticked, and what I can do to make it activated for all Excel I open?

Thanks.
stanley

 

[Dave Peterson]

That event (worksheet_selectionchange) is only going to work for the workbook
that owns the code. And since you've saved it as an addin, that workbook will
never be active.

You'll want to convert your procedure into a application event.

See Chip Pearson's notes:
http://www.cpearson.com/excel/AppEvent.htm

 

[Guest]

Hi Dave,

I don't get it.
How to make it a step by step guide to do it.

Thanks.
stanley

 

[Dave Peterson]

There are events that are limited to a worksheet.
There are events that are limited to a workbook.
There are events that are limited to the application.

You want something that is application wide--can be used on any worksheet in any
application.

I don't think I could do better than what Chip wrote.

But if you find this too intimidating, I think I'd just use Chip's rowliner
addin. You only have to install that and use it.

Hi Dave,

I don't get it.
How to make it a step by step guide to do it.

Thanks.
stanley

 

[Guest]

Hi Dave,
Thanks. I understood now.
And, actually, I just need to copy and paste into whichever worksheet I need.
The colour highlighting is really a good one.

Thanks.
stanley

 

[Dave Peterson]

It would scare me to use this kind of routine for every worksheet in every
workbook.

It could destroy any existing format that I have.

I think using it only the worksheets you want is a much better idea.

Hi Dave,
Thanks. I understood now.
And, actually, I just need to copy and paste into whichever worksheet I need.
The colour highlighting is really a good one.

Thanks.
stanley

 

[jam-acp]

I just downloaded this add-in and set it up in less than two minutes. It
works amazingling well (I'm using Excel 2003) and I already find it helpful.