How To Lock Cells Based On Color In Microsoft Excel
Microsoft Excel lets you apply conditional formatting to cells. If the value in a cell meets certain rules that you’ve set, the conditional formatting feature will change the color of the text in the cell, or the fill color, or both. This is a basic feature that works right out of the box however, if you want to lock cells based on color, you need a little VBA script to do that.
You can use conditional formatting to change the color of a cell or you can manually change the color to anything you want. The VBS script doesn’t depend on conditional formatting to lock cells. It just needs a cell to be colored.
Lock Cells Based On Color
Before you can create a VBA script, you need to know the color of the cell that you’re referencing. VBA doesn’t see colors as they are; it references them via their color codes.
The first thing you need to do is give your cell a color. We’ve gone with the basic yellow which is easy enough to reference. If you’re going with a different color, use this website to find which color code you need to use. The color code we’re using for yellow is #FFFF00.
In Excel, enable the Developer tab, and then switch to it. Click the Visual Basic button and paste the following in ‘This Worksheet’. This script was written by Superuser user Dave.
Sub WalkThePlank() dim colorIndex as Integer colorIndex = FFFF00 Dim rng As Range For Each rng In ActiveSheet.UsedRange.Cells Dim color As Long color = rng.Interior.ColorIndex If (color = colorIndex) Then rng.Locked = True else rng.Locked = false End If Next rng End Sub
Run the script, and it will lock all the cells that are the color you set. The color code you found for your color will be set in this line in the script;
colorIndex = FFFF00
The FFFF00 represents the yellow color and that’s what you need to replace with whatever color code applies to the color you’re using. We should mention that it’s best to go with a basic color. Once you add this, you will need to save your Excel file as a macro enabled file or the VBA script will not save.
The locked cells can easily be unlocked. The lock isn’t a password protected lock the prevents other people from changing the value of a cell if they want. It’s an ordinary lock that prevents accidental changes in the file.
