Can you highlight locked cells in excell, so you can check i
Can you highlight locked cells in excell, so you can check you have locked
the right ones, and after checking turn the highlight off |
Can you highlight locked cells in excell, so you can check i
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 "Tony" wrote: Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off |
Can you highlight locked cells in excell, so you can check i
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.) Tony wrote: Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off -- Dave Peterson |
Can you highlight locked cells in excell, so you can check i
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 featu 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Tony" wrote: Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off |
Can you highlight locked cells in excell, so you can check i
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Tony" wrote: Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off |
Can you highlight locked cells in excell, so you can check i
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Tony" wrote: Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off |
Can you highlight locked cells in excell, so you can check i
But the conditional formatting that I suggested will still indicate whether the
cell is locked -- no matter if the sheet is protected or not. Shane Devenshire wrote: 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 featu 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Tony" wrote: Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off -- Dave Peterson |
Can you highlight locked cells in excell, so you can check i
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. -- Rick (MVP - Excel) "Tony" wrote in message ... Can you highlight locked cells in excell, so you can check you have locked the right ones, and after checking turn the highlight off |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com