hi

Private Sub Worksheet_SelectionChange(ByVal Target _
  As Excel.Range)
    Cells.Interior.ColorIndex = xlNone
    With ActiveCell
        .EntireRow.Interior.ColorIndex = 40
        .EntireColumn.Interior.ColorIndex = 36
    End With
End Sub

the above script works only single worksheet not all workbooks despite
i made adds-in. Please advise that how can i able the above script to
all excel sheets

thanks in advacne

AE

Copy the macro into the worksheet code area of each worksheet.
-- 
Gary''s Student - gsnu2007k

To Esssa and Gary''s Student...

Better would be to remove the code from the current SelectionChange event 
procedure and put it in the workbook's SheetSelectionChange event instead, 
then it would apply to every worksheet in the workbook.

To Esssa...

Take the code out of your current SelectionChange event, double click the 
ThisWorkbook entry in the Project Window on the left, choose Workbook from 
the code window's left hand drop down and SheetSelectionChange from its 
right hand drop down an place the code in there.

-- 
Rick (MVP - Excel)


"Gary''s Student"  wrote in message 
news:7D9071BA-9375-4578-89F9-EE3A04858CA2@microsoft.com...
> Copy the macro into the worksheet code area of each worksheet.
> -- 
> Gary''s Student - gsnu2007k

good idea
-- 
Gary''s Student - gsnu2007k


"Rick Rothstein" wrote:

> To Esssa and Gary''s Student...
> 
> Better would be to remove the code from the current SelectionChange event 
> procedure and put it in the workbook's SheetSelectionChange event instead, 
> then it would apply to every worksheet in the workbook.
> 
> To Esssa...
> 
> Take the code out of your current SelectionChange event, double click the 
> ThisWorkbook entry in the Project Window on the left, choose Workbook from 
> the code window's left hand drop down and SheetSelectionChange from its 
> right hand drop down an place the code in there.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Gary''s Student"  wrote in message 
> news:7D9071BA-9375-4578-89F9-EE3A04858CA2@microsoft.com...
> > Copy the macro into the worksheet code area of each worksheet.
> > -- 
> > Gary''s Student - gsnu2007k 
> 
>

Rick

OP wants to have the code in an add-in to make it available for all open
workbooks/sheets.

Wouldn't that have to be done through Application Event in the add-in?

I'm not sure how to achieve that.

I've been browsing Chip's site but can't get anything to work with my
limited skills.

http://www.cpearson.com/excel/AppEvent.aspx


Gord

On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein"
 wrote:

>To Esssa and Gary''s Student...
>
>Better would be to remove the code from the current SelectionChange event 
>procedure and put it in the workbook's SheetSelectionChange event instead, 
>then it would apply to every worksheet in the workbook.
>
>To Esssa...
>
>Take the code out of your current SelectionChange event, double click the 
>ThisWorkbook entry in the Project Window on the left, choose Workbook from 
>the code window's left hand drop down and SheetSelectionChange from its 
>right hand drop down an place the code in there.

I agree with you Gord (still not your wife, though <vbg>)...

This code would go into the ThisWorkbook module of the addin (that's always
opened when excel opens).

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
    Set xlApp = Application
End Sub
Private Sub Workbook_Close()
    Set xlApp = Nothing
End Sub
Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
                                          ByVal Target As Range)
    Target.Parent.Cells.Interior.ColorIndex = xlNone
    With Target
        .EntireRow.Interior.ColorIndex = 40
        .EntireColumn.Interior.ColorIndex = 36
    End With
End Sub

Personally, I wouldn't use this.  It's not robust enough to clean up after
itself and I wouldn't want to see any of my nicely formatted (pretty colors!)
worksheets be screwed up by this.

Instead, I'd try Chip Pearson's addin:
http://www.cpearson.com/excel/RowLiner.htm

I'm sure he's thought this stuff out way more than I have.


Gord Dibben wrote:
> 
> Rick
> 
> OP wants to have the code in an add-in to make it available for all open
> workbooks/sheets.
> 
> Wouldn't that have to be done through Application Event in the add-in?
> 
> I'm not sure how to achieve that.
> 
> I've been browsing Chip's site but can't get anything to work with my
> limited skills.
> 
> http://www.cpearson.com/excel/AppEvent.aspx
> 
> Gord
> 
> On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein"
>  wrote:
> 
> >To Esssa and Gary''s Student...
> >
> >Better would be to remove the code from the current SelectionChange event
> >procedure and put it in the workbook's SheetSelectionChange event instead,
> >then it would apply to every worksheet in the workbook.
> >
> >To Esssa...
> >
> >Take the code out of your current SelectionChange event, double click the
> >ThisWorkbook entry in the Project Window on the left, choose Workbook from
> >the code window's left hand drop down and SheetSelectionChange from its
> >right hand drop down an place the code in there.

-- 

Dave Peterson

Actually, I didn't read the OP's message... I only reacted to Gary''s 
Student's reply to put the code in each worksheet's SelectionChange event (I 
figured he was responding to the OP's actual question) and realized that the 
workbook's SheetSelectionChange could be used more efficiently than that.

As for doing this through an add-in... I agree with Dave that the OP should 
use the Chip's tried-and-true RowLiner Add-in instead.

-- 
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:3l85c4prsj8k4ps84koh6tp7mspnf1p1cv@4ax.com...
> Rick
>
> OP wants to have the code in an add-in to make it available for all open
> workbooks/sheets.
>
> Wouldn't that have to be done through Application Event in the add-in?
>
> I'm not sure how to achieve that.
>
> I've been browsing Chip's site but can't get anything to work with my
> limited skills.
>
> http://www.cpearson.com/excel/AppEvent.aspx
>
>
> Gord
>
> On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein"
>  wrote:
>
>>To Esssa and Gary''s Student...
>>
>>Better would be to remove the code from the current SelectionChange event
>>procedure and put it in the workbook's SheetSelectionChange event instead,
>>then it would apply to every worksheet in the workbook.
>>
>>To Esssa...
>>
>>Take the code out of your current SelectionChange event, double click the
>>ThisWorkbook entry in the Project Window on the left, choose Workbook from
>>the code window's left hand drop down and SheetSelectionChange from its
>>right hand drop down an place the code in there.
>

Dave and Rick

My first instinct was to direct OP to the rowliner add-in which is not
destructive as the OP code is.

The only catch I find with rowliner is it won't run on a protected
worksheet.

The AppEvent suggestion was for an exercise only.


Gord

On Sat, 06 Sep 2008 11:08:39 -0500, Dave Peterson
 wrote:

>I agree with you Gord (still not your wife, though <vbg>)...
>
>This code would go into the ThisWorkbook module of the addin (that's always
>opened when excel opens).
>
>Option Explicit
>Public WithEvents xlApp As Excel.Application
>Private Sub Workbook_Open()
>    Set xlApp = Application
>End Sub
>Private Sub Workbook_Close()
>    Set xlApp = Nothing
>End Sub
>Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
>                                          ByVal Target As Range)
>    Target.Parent.Cells.Interior.ColorIndex = xlNone
>    With Target
>        .EntireRow.Interior.ColorIndex = 40
>        .EntireColumn.Interior.ColorIndex = 36
>    End With
>End Sub
>
>Personally, I wouldn't use this.  It's not robust enough to clean up after
>itself and I wouldn't want to see any of my nicely formatted (pretty colors!)
>worksheets be screwed up by this.
>
>Instead, I'd try Chip Pearson's addin:
>http://www.cpearson.com/excel/RowLiner.htm
>
>I'm sure he's thought this stuff out way more than I have.
>
>
>Gord Dibben wrote:
>> 
>> Rick
>> 
>> OP wants to have the code in an add-in to make it available for all open
>> workbooks/sheets.
>> 
>> Wouldn't that have to be done through Application Event in the add-in?
>> 
>> I'm not sure how to achieve that.
>> 
>> I've been browsing Chip's site but can't get anything to work with my
>> limited skills.
>> 
>> http://www.cpearson.com/excel/AppEvent.aspx
>> 
>> Gord
>> 
>> On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein"
>>  wrote:
>> 
>> >To Esssa and Gary''s Student...
>> >
>> >Better would be to remove the code from the current SelectionChange event
>> >procedure and put it in the workbook's SheetSelectionChange event instead,
>> >then it would apply to every worksheet in the workbook.
>> >
>> >To Esssa...
>> >
>> >Take the code out of your current SelectionChange event, double click the
>> >ThisWorkbook entry in the Project Window on the left, choose Workbook from
>> >the code window's left hand drop down and SheetSelectionChange from its
>> >right hand drop down an place the code in there.

My guess is that the code the OP is currently using won't run on a protected 
sheet either, so I would think the RowLiner Add-In solution should still be 
a viable choice.

-- 
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:jkd5c4lmlc2lavkok27nm28h3hjniluaj5@4ax.com...
> Dave and Rick
>
> My first instinct was to direct OP to the rowliner add-in which is not
> destructive as the OP code is.
>
> The only catch I find with rowliner is it won't run on a protected
> worksheet.
>
> The AppEvent suggestion was for an exercise only.
>
>
> Gord
>
> On Sat, 06 Sep 2008 11:08:39 -0500, Dave Peterson
>  wrote:
>
>>I agree with you Gord (still not your wife, though <vbg>)...
>>
>>This code would go into the ThisWorkbook module of the addin (that's 
>>always
>>opened when excel opens).
>>
>>Option Explicit
>>Public WithEvents xlApp As Excel.Application
>>Private Sub Workbook_Open()
>>    Set xlApp = Application
>>End Sub
>>Private Sub Workbook_Close()
>>    Set xlApp = Nothing
>>End Sub
>>Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
>>                                          ByVal Target As Range)
>>    Target.Parent.Cells.Interior.ColorIndex = xlNone
>>    With Target
>>        .EntireRow.Interior.ColorIndex = 40
>>        .EntireColumn.Interior.ColorIndex = 36
>>    End With
>>End Sub
>>
>>Personally, I wouldn't use this.  It's not robust enough to clean up after
>>itself and I wouldn't want to see any of my nicely formatted (pretty 
>>colors!)
>>worksheets be screwed up by this.
>>
>>Instead, I'd try Chip Pearson's addin:
>>http://www.cpearson.com/excel/RowLiner.htm
>>
>>I'm sure he's thought this stuff out way more than I have.
>>
>>
>>Gord Dibben wrote:
>>>
>>> Rick
>>>
>>> OP wants to have the code in an add-in to make it available for all open
>>> workbooks/sheets.
>>>
>>> Wouldn't that have to be done through Application Event in the add-in?
>>>
>>> I'm not sure how to achieve that.
>>>
>>> I've been browsing Chip's site but can't get anything to work with my
>>> limited skills.
>>>
>>> http://www.cpearson.com/excel/AppEvent.aspx
>>>
>>> Gord
>>>
>>> On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein"
>>>  wrote:
>>>
>>> >To Esssa and Gary''s Student...
>>> >
>>> >Better would be to remove the code from the current SelectionChange 
>>> >event
>>> >procedure and put it in the workbook's SheetSelectionChange event 
>>> >instead,
>>> >then it would apply to every worksheet in the workbook.
>>> >
>>> >To Esssa...
>>> >
>>> >Take the code out of your current SelectionChange event, double click 
>>> >the
>>> >ThisWorkbook entry in the Project Window on the left, choose Workbook 
>>> >from
>>> >the code window's left hand drop down and SheetSelectionChange from its
>>> >right hand drop down an place the code in there.
>

I recommend this for a protected sheet but there is still some
destruction(see below)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
    If Not OldCell Is Nothing Then
    OldCell.Interior.ColorIndex = xlColorIndexNone
    OldCell.Borders.LineStyle = xlLineStyleNone
    End If
    Set OldCell = Target
    OldCell.Interior.ColorIndex = 6
    OldCell.Borders.LineStyle = xlContinuous
Else
    If OldCell Is Nothing Then
        Set OldCell = Target
    Else
    Set OldCell = Union(OldCell, Target)
    End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

Will color the activecell yellow.

Note:  will wipe out existing background color of activecell cell unless BG
color is due to CF


Gord

On Sat, 6 Sep 2008 13:08:24 -0400, "Rick Rothstein"
 wrote:

>My guess is that the code the OP is currently using won't run on a protected 
>sheet either, so I would think the RowLiner Add-In solution should still be 
>a viable choice.

You can prevent the background color and borders destruction by storing the 
relevant properties in their own Static variables and resetting them before 
updating the OldCell properties. Here is your code modified to do that...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Static OldCell As Range
  Static OldInterior As Long
  Static OldBorders As Long
  If Application.CutCopyMode = 0 Then
    ActiveSheet.Unprotect Password:="justme"
    If Not OldCell Is Nothing Then
      OldCell.Interior.ColorIndex = OldInterior
      OldCell.Borders.LineStyle = OldBorders
    End If
    Set OldCell = Target
    OldInterior = Target.Interior.ColorIndex
    OldBorders = Target.Borders.LineStyle
    OldCell.Interior.ColorIndex = 6
    OldCell.Borders.LineStyle = xlContinuous
  Else
    If OldCell Is Nothing Then
      Set OldCell = Target
    Else
      Set OldCell = Union(OldCell, Target)
    End If
  End If
  ActiveSheet.Protect Password:="justme"
End Sub

Also, you might want to add this line...

If Target.Count <> 1 Then Exit Sub

at the beginning of the code to prevent the error that occurs when multiple 
cells are selected (unless you think it necessary to expand the code to 
handle coloring and re-coloring multiple cells, which would complicate 
things a little).

-- 
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:2ue5c49vm0k3974erosjc1467js0in48s5@4ax.com...
>I recommend this for a protected sheet but there is still some
> destruction(see below)
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Static OldCell As Range
> If Application.CutCopyMode = 0 Then
> ActiveSheet.Unprotect Password:="justme"
>    If Not OldCell Is Nothing Then
>    OldCell.Interior.ColorIndex = xlColorIndexNone
>    OldCell.Borders.LineStyle = xlLineStyleNone
>    End If
>    Set OldCell = Target
>    OldCell.Interior.ColorIndex = 6
>    OldCell.Borders.LineStyle = xlContinuous
> Else
>    If OldCell Is Nothing Then
>        Set OldCell = Target
>    Else
>    Set OldCell = Union(OldCell, Target)
>    End If
> End If
> ActiveSheet.Protect Password:="justme"
> End Sub
>
> Will color the activecell yellow.
>
> Note:  will wipe out existing background color of activecell cell unless 
> BG
> color is due to CF
>
>
> Gord
>
> On Sat, 6 Sep 2008 13:08:24 -0400, "Rick Rothstein"
>  wrote:
>
>>My guess is that the code the OP is currently using won't run on a 
>>protected
>>sheet either, so I would think the RowLiner Add-In solution should still 
>>be
>>a viable choice.
>

Thanks Rick

Appreciate the updates.


Gord

On Sat, 6 Sep 2008 13:45:54 -0400, "Rick Rothstein"
 wrote:

>You can prevent the background color and borders destruction by storing the 
>relevant properties in their own Static variables and resetting them before 
>updating the OldCell properties. Here is your code modified to do that...
>
>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>  Static OldCell As Range
>  Static OldInterior As Long
>  Static OldBorders As Long
>  If Application.CutCopyMode = 0 Then
>    ActiveSheet.Unprotect Password:="justme"
>    If Not OldCell Is Nothing Then
>      OldCell.Interior.ColorIndex = OldInterior
>      OldCell.Borders.LineStyle = OldBorders
>    End If
>    Set OldCell = Target
>    OldInterior = Target.Interior.ColorIndex
>    OldBorders = Target.Borders.LineStyle
>    OldCell.Interior.ColorIndex = 6
>    OldCell.Borders.LineStyle = xlContinuous
>  Else
>    If OldCell Is Nothing Then
>      Set OldCell = Target
>    Else
>      Set OldCell = Union(OldCell, Target)
>    End If
>  End If
>  ActiveSheet.Protect Password:="justme"
>End Sub
>
>Also, you might want to add this line...
>
>If Target.Count <> 1 Then Exit Sub
>
>at the beginning of the code to prevent the error that occurs when multiple 
>cells are selected (unless you think it necessary to expand the code to 
>handle coloring and re-coloring multiple cells, which would complicate 
>things a little).