Lock and unlock cells in Excel based on color in all worksheets

0 votes

To unlock all yellow cells (colour index 36) and lock all non-yellow cells, I'm attempting to develop a vba macro. The code below runs, but I'm having trouble putting it in a for loop so that it runs through all of the sheets in my workbook rather than just "Sheet1."

Sub test()

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    For Each cell In Sheet1.UsedRange
        If cell.Interior.ColorIndex = 36 Then
            cell.Locked = False
        End If
    Next

    ws.Protect userinterfaceonly:=True

End Sub
Feb 11 in Others by narikkadan
• 59,740 points
56 views

1 answer to this question.

0 votes

The following should function if you actually want this to execute on every worksheet in your workbook:

Sub test()
    'declarations
    Dim ws As Worksheet
    'loop through each worksheet (ws) in active workbook
    For Each ws In ActiveWorkbook.Worksheets
        'remove protection
        ws.Unprotect
        'lock all cells
        ws.UsedRange.Cells.Locked = True
        'loop through used range
        For Each cell In ws.UsedRange
            'unlock cells based on color index
            If cell.Interior.ColorIndex = 36 Then cell.MergeArea.Locked = False
        Next
        'reprotect sheet
        ws.Protect userinterfaceonly:=True
    Next
End Sub
answered Feb 11 by Kithuzzz
• 34,760 points

Related Questions In Others

0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24 in Others by narikkadan
• 59,740 points
94 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 59,740 points
343 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 59,740 points
1,948 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
575 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,548 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
148 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
391 views
0 votes
1 answer
0 votes
1 answer

Check multiple cells in excel, move to next cell if null and if value found stop and return that value

Use this formula: =INDEX(FILTER(A2:D2,A2:D2<>""),1,1) INDEX returns the first value ...READ MORE

answered 3 days ago in Others by Kithuzzz
• 34,760 points
14 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP