1. Home
  2. MS Office
  3. Lock cells based on color microsoft excel

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
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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.