• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

Excel VB

Hi I want to lock all the cells in a spreadsheet when I protect sheets that are not white.  The code below kind of works but I need to lock it for every other colour and then it unlocks the previous ones so need the code below to run for everything not equal to white
-4142    I think   or to be able to run per singular colour without unlocking the others - then I can go through one by one.  Thanks

  Sub Colchange()

Dim colorIndex As Integer
colorIndex = 1

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
0
RichardAtk
Asked:
RichardAtk
1 Solution
 
Rgonzo1971Commented:
Hi.

Pls try
Sub Colchange()

 Dim colorIndex
 Dim rng As Range
ActiveSheet.Cells.Locked = False
 For Each rng In ActiveSheet.UsedRange.Cells
    If rng.Interior.colorIndex <> xlColorIndexNone Then
        If (rng.MergeCells) Then
            rng.MergeArea.Locked = True
        Else
            rng.Locked = True
        End If
    End If
Next rng

 End Sub

Open in new window

Regards
0
 
RichardAtkAuthor Commented:
Perfect many thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now