software

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; Cells.FormatConditions.Delete
gt; With Target.EntireRow
gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; With .FormatConditions(1)
gt; With .Borders(xlTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 5
gt; End With
gt; With .Borders(xlBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = 5
gt; End With
gt; End With
gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; End With
gt;
gt; End Sub
How can I make this work with a write protected worksheet?

Hi Ed,

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect quot;YourPassordquot;

'your code

Me.Protect quot;YourPassordquot;

End Sub---
Regards,
Normanquot;Ed Tquot; lt;Ed gt; wrote in message
...
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt;gt; Cells.FormatConditions.Delete
gt;gt; With Target.EntireRow
gt;gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt;gt; With .FormatConditions(1)
gt;gt; With .Borders(xlTop)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThin
gt;gt; .ColorIndex = 5
gt;gt; End With
gt;gt; With .Borders(xlBottom)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThin
gt;gt; .ColorIndex = 5
gt;gt; End With
gt;gt; End With
gt;gt; .FormatConditions(1).Interior.ColorIndex = 20
gt;gt; End With
gt;gt;
gt;gt; End Sub
gt; How can I make this work with a write protected worksheet?
This code works good, is there anyway to keep the formulas from showing in
the formula bar?

quot;Norman Jonesquot; wrote:

gt; Hi Ed,
gt;
gt; Try:
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt;
gt; Me.Unprotect quot;YourPassordquot;
gt;
gt; 'your code
gt;
gt; Me.Protect quot;YourPassordquot;
gt;
gt; End Sub
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt; quot;Ed Tquot; lt;Ed gt; wrote in message
gt; ...
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt;gt; Cells.FormatConditions.Delete
gt; gt;gt; With Target.EntireRow
gt; gt;gt; .FormatConditions.Add Type:=xlExpression, Formula1:=quot;TRUEquot;
gt; gt;gt; With .FormatConditions(1)
gt; gt;gt; With .Borders(xlTop)
gt; gt;gt; .LineStyle = xlContinuous
gt; gt;gt; .Weight = xlThin
gt; gt;gt; .ColorIndex = 5
gt; gt;gt; End With
gt; gt;gt; With .Borders(xlBottom)
gt; gt;gt; .LineStyle = xlContinuous
gt; gt;gt; .Weight = xlThin
gt; gt;gt; .ColorIndex = 5
gt; gt;gt; End With
gt; gt;gt; End With
gt; gt;gt; .FormatConditions(1).Interior.ColorIndex = 20
gt; gt;gt; End With
gt; gt;gt;
gt; gt;gt; End Sub
gt; gt; How can I make this work with a write protected worksheet?
gt;
gt;
gt;

Hi Ed,

gt; This code works good, is there anyway to keep the formulas from
gt; showing in the formula bar?

Try something like:

'=============gt;gt;
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Const PWORD As String = quot;ABCquot; 'lt;lt;==== CHANGE

Set SH = ThisWorkbook.Sheets(quot;Sheet1quot;) 'lt;lt;==== CHANGE

SH.Unprotect Password:=PWORD
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
With rng
.Locked = True
.FormulaHidden = True
End With
End If

SH.Protect Password:=PWORD

End Sub
'lt;lt;=============

Incidentally, it is advisable to post separate questions in a new thread.
This is not only to accord with established group etiquette but to
facilitate coherent Google storage and, not least, to maximise your
prospects of receiving viable assistance.---
Regards,
Norman
Hi Norman,

Thanks for your help; I was able to make it work by allowing user to format
cells on the protection box.

Regards,
Ed

quot;Norman Jonesquot; wrote:

gt; Hi Ed,
gt;
gt; gt; This code works good, is there anyway to keep the formulas from
gt; gt; showing in the formula bar?
gt;
gt; Try something like:
gt;
gt; '=============gt;gt;
gt; Public Sub Tester()
gt; Dim SH As Worksheet
gt; Dim rng As Range
gt; Const PWORD As String = quot;ABCquot; 'lt;lt;==== CHANGE
gt;
gt; Set SH = ThisWorkbook.Sheets(quot;Sheet1quot;) 'lt;lt;==== CHANGE
gt;
gt; SH.Unprotect Password:=PWORD
gt; On Error Resume Next
gt; Set rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
gt; On Error GoTo 0
gt;
gt; If Not rng Is Nothing Then
gt; With rng
gt; .Locked = True
gt; .FormulaHidden = True
gt; End With
gt; End If
gt;
gt; SH.Protect Password:=PWORD
gt;
gt; End Sub
gt; 'lt;lt;=============
gt;
gt; Incidentally, it is advisable to post separate questions in a new thread.
gt; This is not only to accord with established group etiquette but to
gt; facilitate coherent Google storage and, not least, to maximise your
gt; prospects of receiving viable assistance.
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt;