I have a spreadsheet with over 15 tabs that I use to gather budget data. But all the tabs have data in different places. Is there a way to lock cells based on a cell color? I know how to lock cells individually, but it takes so long to do each spreadsheet differently.
Thanks
The only way that you could get the coloured cells would be individually, so that would be no better.
It's a quiet afternoon, so here's a way do deal with a selection of colours.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
How about if I want to only have cells with a specific color unlocked and everything else locked?
Thanks
A slight change is required to my earlier code
[VBA]Sub ColLock()
Dim Cel As Range, c As Range, Col As Long, Lkd As Boolean
Dim sh As Worksheet
Set Cel = ActiveCell
If Cel.Interior.ColorIndex = xlNone Then
MsgBox "Select coloured cell"
Exit Sub
End If
Application.ScreenUpdating = False
Lkd = Cel.Locked
Col = Cel.Interior.ColorIndex
For Each sh In Worksheets
sh.Unprotect
For Each c In sh.UsedRange
If c.Interior.ColorIndex = Col Then
c.Locked = Not Lkd
End If
Next
Next
If Lkd = False Then
Cel.Offset(, 1) = "Locked"
Else
Cel.Offset(, 1) = "Open"
End If
For Each sh In Worksheets
sh.Protect
Next
Application.ScreenUpdating = True
End Sub
[/VBA]
To lock all cells
[VBA]Sub LockAll()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Unprotect
sh.Cells.Locked = True
sh.Protect
Next
With Sheets(1)
.Unprotect
.Range("B1:B20") = "Locked"
.Protect
End With
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Which is doing it individually, whereas I thought the OP said he knew how to do that?
I read it as doing it manually, hence my response. The OP can let us know!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Not quite sure I understand. I would like all cells locked (including cells with no shading) except for cells highlighted in green or yellow.
This example shows one way of accomplising what you seem to be after. I don't know how many colours you want to use, our how you want to trigger the changes.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Jerry,
you need to select one of the highlighted cells before running Malcolms code. It only works for one(the activecell)color.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
It works, thanks. How can I hide this from others using the template? Is there a way of protecting this sheet, or a password or something else?
Thanks
Here's a protected version. The button sheet is simply hidden. VBA code and sheet passwords is MDM. This can be changed here.
[vba]Option Explicit
Const PW = "MDM"[/vba] You should be aware that Excel protection is very insecure.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Indeed, protection is essentially there to protect your project from accidental changes, not to "secure" it. Have a read hereOriginally Posted by mdmackillop
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Posting Permissions
Reply With Quote
