Prev: SumIf
Next: Auto Calculate Keeps Resetting to Manual
From: Eurika on 14 Apr 2010 06:48
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/aspnet/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-auto.aspx
From: Gord Dibben on 14 Apr 2010 20:25
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/aspnet/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-auto.aspx

From: Gord Dibben on 15 Apr 2010 11:23
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/aspnet/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-auto.aspx

 | 
Pages: 1
Prev: SumIf
Next: Auto Calculate Keeps Resetting to Manual