3

I have this excel sheet where i want to protect some cells from formating and editing. All these cells are colored with a specific color.

The sheet is very big and therefor i am looking for a way to lock all these cells in a go and then be able to mass format all the other cells without changing the cells that i want to lock.

Is there some way to tell excel to lock cells with a specific color?

5

Yes, with VBa... Simply copy this into the "ThisWorkbook" in the Visual Basic Screen and then run it (green play triangle)

enter image description here

Sub WalkThePlank()

    dim colorIndex as Integer
    colorIndex = 3                   'UPDATE ME TO YOUR COLOUR OR BE FED TO THE SHARKS   

    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    'this will remove any locks for those not in the given color
        End If

    Next rng

End Sub

There is no undo in VBa, so take a copy of your file first (to create a back up)!

Colour index - http://dmcritchie.mvps.org/excel/colors.htm

How do I add VBA in MS Office?

The above assumes you have no merged cells and that your worksheet is not protected.

If you are not sure what the colorIndex you need is, then use this script first

Sub Find()

Dim colorIndexFinder As Integer
colorIndexFinder = Range("A1").Interior.colorIndex  'CHANGE A1 to the cell with the colour you want to use
MsgBox (colorIndexFinder)

End Sub

Edit

You have mentioned you do use merged cells

Please try

Sub WalkThePlank()

Dim colorIndex As Integer
colorIndex = 3                   'UPDATE ME TO YOUR COLOUR OR BE FED TO THE SHARKS

Dim rng As Range

For Each rng In ActiveSheet.UsedRange.Cells

    Dim color As Long
    color = rng.Interior.colorIndex

    If (color = colorIndex) Then
        If (rng.MergeCells) Then
            rng.MergeArea.Locked = True
        Else
            rng.Locked = True
        End If
    Else
        If (rng.MergeCells) Then
            rng.MergeArea.Locked = False
        Else
            rng.Locked = False
        End If
    End If

    Next rng

End Sub
11
  • Your solution looks familiar, Dave ^^ Jun 2 '15 at 7:41
  • 2
    It is @duDE, but, I wrote mine, I didn't copy and paste :) And when I posted my answer, your answer wasn't visible (you had answered it but I had to refresh my page to see your answer), I assure you I didn't just copy your answer!
    – Dave
    Jun 2 '15 at 7:44
  • 2
    Never mind, I see :) +1 from me! Jun 2 '15 at 7:52
  • 1
    Are you using merged cells @Behedwin?
    – Dave
    Jun 2 '15 at 8:01
  • 1
    @Behedwin, try it now, added a new script
    – Dave
    Jun 2 '15 at 9:26
2

I have found this way by using a simple macro:

Select the whole sheet (Ctrl+A) and unlock all cells, and then use this macro to set the coloured ones to be locked again:

Dim c As Object 
For Each c In selection 
    If c.ColorIndex = 6 ' 6 is for Yellow - change to the colour you want
    c.Locked = True 
End If 
Next c 
2
  • Thank you. But if you could explain how i run this macro that would be great. I have no idea why it wont work when i copy paste it into the visual basic module. it gives an error....
    – Behedwin
    Jun 2 '15 at 7:42
  • Take a look: excel-easy.com/vba/create-a-macro.html , it's really simple, try it! Jun 2 '15 at 7:51
1

Vba solution (How do I add VBA in MS Office?)

Sub LockOnlyCellsWithCertainColor()
    'Change to your color
    Const colorToLock = 65535

    Dim currentCell As Range

    ActiveSheet.Cells.Locked = False

    For Each currentCell In ActiveSheet.UsedRange.Cells
        If currentCell.Interior.Color = colorToLock Then
            If currentCell.MergeCells Then
                currentCell.MergeArea.Locked = True
            Else
                currentCell.Locked = True
            End If
        End If
    Next

End Sub

Sub GetBackgroundColorOfActiveCell()
    Debug.Print ActiveCell.Interior.Color
    MsgBox ActiveCell.Interior.Color
End Sub
0

The below works for me as long as you unprotect the sheet first, color Index is set to 6 for yellow.

Sub Lock_by_Color()
Dim colorIndex As Integer
Dim Range As Range

colorIndex = 6
For Each Range In ActiveSheet.UsedRange.Cells
Dim color As Long
 color = Range.Interior.colorIndex
If (color = colorIndex) Then
 Range.Locked = True
Else
 Range.Locked = False
End If
Next Range

ActiveSheet.Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlNoRestrictions
End Sub
1
  • This looks essentially the same as Dave's answer from 3 yrs ago.
    – fixer1234
    May 24 '18 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.