Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change cell color
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
change cell color
Hi Ed,
Try: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Unprotect "YourPassord" 'your code Me.Protect "YourPassord" End Sub --- Regards, Norman "Ed T" <Ed wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
change cell color
This code works good, is there anyway to keep the formulas from showing in
the formula bar? "Norman Jones" wrote: Hi Ed, Try: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Unprotect "YourPassord" 'your code Me.Protect "YourPassord" End Sub --- Regards, Norman "Ed T" <Ed wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
change cell color
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. --- Regards, Norman |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
change cell color
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 "Norman Jones" wrote: 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. --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
cell pointer color change | Setting up and Configuration of Excel | |||
How to change the default Border, Font Color, and Cell Color | Excel Discussion (Misc queries) | |||
How do I change the color of the indicator cell in row and column | Excel Worksheet Functions | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |