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, 2023 in Others by narikkadan
• 63,600 points
761 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, 2023 by Kithuzzz
• 38,000 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, 2023 in Others by narikkadan
• 63,600 points
615 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
• 63,600 points
1,222 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
• 63,600 points
8,772 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
1,259 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
3,697 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
• 23,260 points
974 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,690 points
1,089 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,000 points
569 views
0 votes
1 answer
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