Ad-In Rowliner

How do I prevent the background from changing when I use Row-Liner in a protected worksheet. I used the following VB Code, but the backgrounds I used changed to white.

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




Submitted via EggHeadCafe - Software Developer Portal of Choice
Using VSTO Add-In To Automate Frequent Excel 2007 Tasks
http://www.eggheadcafe.com/tutorials...n-to-auto.aspx

That is not Chip's RowLiner add-in.

It is event code to be used in place of the Add-in because the Add-in won't
work on protected sheets.

One disadvantage of the code you are using is the wiping out of existing
background colors.


Gord Dibben MS Excel MVP

On Wed, 14 Apr 2010 03:48:20 -0700, Eurika Stemmet wrote:

How do I prevent the background from changing when I use Row-Liner in a protected worksheet. I used the following VB Code, but the backgrounds I used changed to white.

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




Submitted via EggHeadCafe - Software Developer Portal of Choice
Using VSTO Add-In To Automate Frequent Excel 2007 Tasks
http://www.eggheadcafe.com/tutorials...n-to-auto.aspx

How can I highlight the active row in a protected excel worksheet?



Gord Dibben wrote:

That is not Chip's RowLiner add-in.
14-Apr-10

That is not Chip's RowLiner add-in.

It is event code to be used in place of the Add-in because the Add-in will not
work on protected sheets.

One disadvantage of the code you are using is the wiping out of existing
background colors.


Gord Dibben MS Excel MVP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF GridView Sample To Insert, Update, and Delete Records
http://www.eggheadcafe.com/tutorials...ple-to-in.aspx

This event code from Mike H will not wipe out existing formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'row highlighter
'Mike H.........does not destroy existing formats
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
.Interior.ColorIndex = 20
End With
End With
End Sub


Gord

On Wed, 14 Apr 2010 17:25:14 -0700, Gord Dibben gorddibbATshawDOTca wrote:

That is not Chip's RowLiner add-in.

It is event code to be used in place of the Add-in because the Add-in won't
work on protected sheets.

One disadvantage of the code you are using is the wiping out of existing
background colors.


Gord Dibben MS Excel MVP

On Wed, 14 Apr 2010 03:48:20 -0700, Eurika Stemmet wrote:

How do I prevent the background from changing when I use Row-Liner in a protected worksheet. I used the following VB Code, but the backgrounds I used changed to white.

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




Submitted via EggHeadCafe - Software Developer Portal of Choice
Using VSTO Add-In To Automate Frequent Excel 2007 Tasks
http://www.eggheadcafe.com/tutorials...n-to-auto.aspx

Go back to your first post.

I added a reply with code a few minutes ago.


Gord

On Thu, 15 Apr 2010 06:00:28 -0700, Eurika Stemmet wrote:

How can I highlight the active row in a protected excel worksheet?



Gord Dibben wrote:

That is not Chip's RowLiner add-in.
14-Apr-10

That is not Chip's RowLiner add-in.

It is event code to be used in place of the Add-in because the Add-in will not
work on protected sheets.

One disadvantage of the code you are using is the wiping out of existing
background colors.


Gord Dibben MS Excel MVP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF GridView Sample To Insert, Update, and Delete Records
http://www.eggheadcafe.com/tutorials...ple-to-in.aspx