Edit MACRO for all SHEETS
 

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

Edit MACRO for all SHEETS
 

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.


--
Regards
Frank Kabel
Frankfurt, Germany


DAA wrote:
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

Edit MACRO for all SHEETS
 

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

"DAA" wrote in message
...
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

Edit MACRO for all SHEETS
 

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" wrote in message
...
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

Edit MACRO for all SHEETS
 

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


-----Original Message-----
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" wrote in
message
...
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






.

Edit MACRO for all SHEETS
 

Hi Bob,
It's working perfectly! Please disregard my previous
posting.
Thank you for your time and effort.
Regards,
DAA
-----Original Message-----
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" wrote in
message
...
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






.

Edit MACRO for all SHEETS
 

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)

"DAA" wrote in message
...
Hi Bob,
It's working perfectly! Please disregard my previous
posting.
Thank you for your time and effort.
Regards,
DAA
-----Original Message-----
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" wrote in
message
...
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






.