Can you highlight locked cells in excell, so you can check i

[Tony]

Can you highlight locked cells in excell, so you can check you have locked
the right ones, and after checking turn the highlight off

 

[Mike H]

Tony,

I assume you understand that being 'Locked' doesn't mean they are protected.
All cells by default are locked but that only becomes active after you
protect the sheet.

To hightlight locked cells then you would need code. Right click your sheet
tab, view code and paste the code below in and run it
.. To remove the highlighting simply slect all cells and remove the fill
colour on the toolbar.

Sub Sonic()
For Each c In ActiveSheet.UsedRange
If c.Locked = True Then c.Interior.ColorIndex = 3
Next
End Sub

 

[Dave Peterson]

Are you using Format|Conditional Formatting for anything?

If no, then how about using it to show the locked/unlocked cells?

Select your range (ctrl-A for all the cells).
Format|Conditional formatting
formula is: =CELL("protect",A1)

(Use the activecell's address instead of A1.)

 

[Shane Devenshire]

As the two answers suggest Locked <> Protected. To indicate Locked cells
regardless of protection using conditional formatting will require a little
more work.

Also, it sounds like you want to toggle this feature:

Sub Locked()
Dim cell As Range
Dim c As Range
Dim myNoColor As Boolean
For Each cell In Selection
If cell.Locked And cell.Interior.ColorIndex <> 3 Then
myNoColor = True
Exit For
End If
Next cell
If myNoColor = True Then
For Each cell In Selection
If cell.Locked Then cell.Interior.ColorIndex = 3
Next cell
Else
Selection.Interior.ColorIndex = xlColorIndexNone
End If
End Sub

There is a major potential problem with using this approach - your cells
will loose any default fill colors.

 

[Shane Devenshire]

Hi,

Back again,

To handle this with conditional formatting (which will not affect the
underlying fill color:

First define a range name -
1. Choose Insert, Name, Define
2. and in the Names in workbook box enter myColor (or any name you want)
3. in the Refers to box enter
=GET.CELL(14,INDIRECT("rc",0))
Next apply conditional formatting by selecting the range you want to check
and choosing
4. Format, Conditional Formatting
5. Pick Formula is from the first drop down
6. In the next box enter
=myLocked=TRUE
7. Click Format and pick a color

You will need to clear the conditional formatting to turn it off. You could
put that into a macro.

 

[Shane Devenshire]

Hi,

And some more stuff:

If you want a toggle macro to apply conditional formatting to some selected
cells if they are locked:

Sub ColorMyLocked()
With Selection
If .FormatConditions.Count > 0 Then
.FormatConditions.Delete
Else
.FormatConditions.Delete
.FormatConditions(1).Interior.ColorIndex = 38
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=myLocked=TRUE"
End If
End With
End Sub

Note just like the color fill macro in the last email, if you have
conditional formats set on this same range then this will remove them,
however, it will not effect the fill colors which you apply manually. This
is a toggle, so you just rerun it to remove the conditional formatting.

 

[Dave Peterson]

But the conditional formatting that I suggested will still indicate whether the
cell is locked -- no matter if the sheet is protected or not.

 

[Rick Rothstein]

Click Edit/Find on Excel's menu bar. If all the options are not showing in
the dialog box, then click the Options>> button. Make sure the "Find what"
field is empty and click the Format button. Go to the Protection tab and
make Locked display a check mark and make Hidden not checked. Press OK to go
back to the Find dialog. Select Sheet in the Within dropdown box and Values
from the LookIn dropdown box. Then click the Find All button and then press
Ctrl+A. You can now click the Close button... all your Locked cells should
be selected.