Edit MACRO for all SHEETS

[DAA]

The macro below changes the cell pointer color. However,
it only works on A sheet in a workbook. I need the macro
to work in ALL the sheets in the workbook without pasting
the macro on all worksheet modules.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Not OldRng Is Nothing Then
OldRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng = Target
End Sub

Please edit MACRO to work on all sheets. Thanks

 

[Frank Kabel]

Hi
as this is an event procedure on worksheet level you have to insert
this procedure in every worksheet module.
You may take a look at the following add-in:
http://www.cpearson.com/excel/RowLiner.htm

Just a note: Using this add-in will disable the Undo functionality of
your workbook.

 

[Nigel]

Not entirely an answer but at least the following method means a common
procedure can be modified which then applies to all sheets with the smaller
(common) code behind each sheet

I put your code into a module (respecify as a Public procedure not Private)
and changed the parameter as shown

Public OldRng As Range
Public Sub WSChange(Target)
If Not OldRng Is Nothing Then
OldRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng = Target
End Sub

I then put the following code behind each worksheet (same for all
worksheets) where you wish this to apply .......

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call WSChange(Target)
End Sub

If you allow users to create new worksheets then the module needs another
procedure to automatically populate the above code behind it, which would be
initiated by a workbook level event.

Cheers
Nigel

 

[Bob Phillips]

Hi DAA,

Excel also provides workbook wide sheet events. So put this code in the
ThisWorkbook code module

Public OldRng

Private Sub Workbook_Open()
ReDim OldRng(20) 'allow for upto 20 worksheets
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim ThisRng

On Error Resume Next
Set ThisRng = OldRng(Sh.Index)
If Not ThisRng Is Nothing Then
ThisRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng(Sh.Index) = Target

End Sub


--

HTH

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

 

[DAA]

Hi Bob,

I copied and pasted the 2 macros you provided in the
WORKBOOK module. It gave me an error message "invalid
Outside procedure". It high-lighted the "ReDim OldRng(20)"

Can you please take a look again? Thanks.

Regards,
DAA

 

[DAA]

Hi Bob,
It's working perfectly! Please disregard my previous
posting.
Thank you for your time and effort.
Regards,
DAA

 

[Bob Phillips]

Phew! Had me worried there.

--

HTH

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