Solved

Excel VB

Posted on 2016-11-03
2
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 51

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

Technology Partners: 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!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

695 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