change cell color

[Guest]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

End Sub

How can I make this work with a write protected worksheet?

 

[Norman Jones]

Hi Ed,

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect "YourPassord"

'your code

Me.Protect "YourPassord"

End Sub

 

[Guest]

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

 

[Norman Jones]

Hi Ed,

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

Try something like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Const PWORD As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== 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
'<<=============

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.

 

[Guest]

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