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.Cell s
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
-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.Cell
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER