How do U Color shade Locked and FormulaHidden Cells
 

Hi all,
How do i color shade cells say yellow in a sheet that does not have
locked and FormulaHidden. The idea is to see at a glance which cells
are editable. Many thks KZ

How do U Color shade Locked and FormulaHidden Cells
 

Hi Kieranz,

you can use this:

Function IsHiddenOrLocked(Target As Range) As Boolean
Dim varPom As Variant
Set Target = Target.Cells(1, 1)
varPom = (Target.Locked = True) Or (Target.FormulaHidden = True)
If IsNull(varPom) Then
IsHiddenOrLocked = False
Else
IsHiddenOrLocked = varPom
End If
End Function

Sub FormatHiddenOrLocked()
Dim Target As Range
Dim Cel As Range

Set Target = Intersect(Selection, Selection.Parent.UsedRange)
For Each Cel In Target
If IsHiddenOrLocked(Cel) Then Cel.Interior.ColorIndex = 6
Next Cel
End Sub

Regards,
Ivan

How do U Color shade Locked and FormulaHidden Cells
 

Hi Kieranz,

Try something like:

'=============
Public Sub ToggleLockedCellsColor()
Dim rCell As Range
Dim iColor As Long
Dim bNoColor As Boolean
Dim bLocked As Boolean
Dim res As String

res = InputBox("Enter the sheet protection password")

iColor = 6 '<<==== Yellow - Change to taste
bLocked = False

On Error Resume Next
ActiveSheet.Unprotect res
If Err.Number < 0 Then
MsgBox "Password not recognised"
Exit Sub
End If

For Each rCell In ActiveSheet.UsedRange.Cells
With rCell
If .Locked Then
bNoColor = rCell.Interior.ColorIndex = xlNone
bLocked = True
Exit For
End If
End With
Next rCell

If Not bLocked Then
MsgBox Prompt:="No locked cells found!", _
Buttons:=vbInformation, _
Title:="Locked Cells"
End If

For Each rCell In ActiveSheet.UsedRange.Cells
With rCell
If .Locked Then
.Interior.ColorIndex = IIf(bNoColor, iColor, xlNone)
End If
End With
Next

ActiveSheet.Protect res

End Sub
'<<=============

See also:

http://tinyurl.com/syl22


---
Regards,
Norman


"Kieranz" wrote in message
oups.com...
Hi all,
How do i color shade cells say yellow in a sheet that does not have
locked and FormulaHidden. The idea is to see at a glance which cells
are editable. Many thks KZ

How do U Color shade Locked and FormulaHidden Cells
 

Many thks Ivan and Norman
Just great
Rgds
KZ