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
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
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
Many thks Ivan and Norman
Just great
Rgds
KZ
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks