Solved

Excel VB

Posted on 2016-11-03
2
33 Views
Last Modified: 2016-11-03
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
Comment
Question by:RichardAtk
2 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41872127
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
 

Author Closing Comment

by:RichardAtk
ID: 41872148
Perfect many thanks
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Statements 11 40
Excel User Form VBA Help 18 32
Excel - Data Validation 3 29
Copying from excel I am getting extra text 11 44
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question