How To Lock Cells Based On Color In Microsoft Excel [Guide]

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

‘); if (navigator.appVersion.indexOf(“Mac”)!=-1) document.write(”);

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.

About Alex Miller 12813 Articles
Alex Miller is a bestselling Tech author currently working here. His work has received a lot of appreciation from users and is being made to work hard to meet the user's expectations.

Be the first to comment

Leave a Reply

Your email address will not be published.