{"id":1921,"date":"2022-08-07T10:07:23","date_gmt":"2022-08-07T07:07:23","guid":{"rendered":"https:\/\/www.addictivetips.com\/?p=266303https:\/\/www.addictivetips.com\/microsoft-office\/lock-cells-based-on-color-microsoft-excel\/"},"modified":"2022-08-07T10:07:23","modified_gmt":"2022-08-07T07:07:23","slug":"how-to-lock-cells-based-on-color-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/tojikon.net\/en\/1921-how-to-lock-cells-based-on-color-in-microsoft-excel\/","title":{"rendered":"How To Lock Cells Based On Color In Microsoft Excel"},"content":{"rendered":"

Microsoft Excel lets you apply conditional formatting to cells. If the value in a cell meets certain rules that you\u2019ve 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.<\/p>\n

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\u2019t depend on conditional formatting to lock cells. It just needs a cell to be colored.<\/p>\n

Lock Cells Based On Color<\/h2>\n

Before you can create a VBA script, you need to know the color of the cell that you\u2019re referencing. VBA doesn\u2019t see colors as they are; it references them via their color codes.<\/p>\n

The first thing you need to do is give your cell a color. We\u2019ve gone with the basic yellow which is easy enough to reference. If you\u2019re going with a different color, use this website to find which color code you need to use. The color code we\u2019re using for yellow is\u00a0 #FFFF00.<\/p>\n

In Excel, enable the Developer tab, and then switch to it. Click the Visual Basic button and paste the following in \u2018This Worksheet\u2019. This script was written by Superuser user Dave.<\/p>\n

Sub WalkThePlank() dim colorIndex as Integer\ncolorIndex = FFFF00 Dim rng As Range For Each rng In ActiveSheet.UsedRange.Cells Dim color As Long\ncolor = rng.Interior.ColorIndex\nIf (color = colorIndex) Then rng.Locked = True\nelse\nrng.Locked = false End If Next rng End Sub<\/pre>\n
\"\"<\/span><\/p>\n
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;<\/p>\n
colorIndex = FFFF00<\/pre>\n
The FFFF00 represents the yellow color and that\u2019s what you need to replace with whatever color code applies to the color you\u2019re using. We should mention that it\u2019s best to go with a basic color.\u00a0Once you add this, you will need to save your Excel file as a macro enabled file or the VBA script will not save.<\/p>\n
The locked cells can easily be unlocked. The lock isn\u2019t a password protected lock the prevents other people from changing the value of a cell if they want. It\u2019s an ordinary lock that prevents accidental changes in the file.<\/p>\n","protected":false},"excerpt":{"rendered":"
Microsoft Excel lets you apply conditional formatting to cells. If the value in a cell meets certain rules that you\u2019ve 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 …<\/p>\n","protected":false},"author":1,"featured_media":1922,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[23],"tags":[36],"_links":{"self":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/posts\/1921"}],"collection":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/comments?post=1921"}],"version-history":[{"count":0,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/posts\/1921\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/media\/1922"}],"wp:attachment":[{"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/media?parent=1921"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/categories?post=1921"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tojikon.net\/en\/wp-json\/wp\/v2\/tags?post=1921"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}