Link to home
Start Free TrialLog in
Avatar of RichardAtk
RichardAtk

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RichardAtk
RichardAtk

ASKER

Perfect many thanks