none
Excel VBA code to Protect Merge cells do not work RRS feed

  • Question

  • Hi All,

    I have below code which checks each cell in all the sheets and if any cell is having some color than it unlocks that cell. The code is working properly in sheets that have merge cells but code throws error on sheet that do not have merge cells.

    Error- Run time error 1004- Unable to set the locked property of the range class

    Dim ws As Worksheet
    Dim CELL As Range
    
    For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    
    For Each CELL In ActiveSheet.UsedRange
    
    If CELL.Interior.Color = 15921906 Or CELL.Interior.Color = 14408946 Then
    CELL.MergeArea.Locked = False
    Else
    CELL.MergeArea.Locked = True
    
    End If
    Next
    Next

    Thanks,

    Zaveri

    Wednesday, November 12, 2014 5:03 PM

Answers

  • Hi Zaveri,

    I tried your code and it works just fine. So i think there is something else that 'bugs' your code. I think you might have a named range that could be an issue. Tell us some more about the layout of your worksheets.


    Maurice

    • Marked as answer by zaveri cc Wednesday, November 12, 2014 9:01 PM
    Wednesday, November 12, 2014 5:53 PM

All replies

  • Hi Zaveri,

    I tried your code and it works just fine. So i think there is something else that 'bugs' your code. I think you might have a named range that could be an issue. Tell us some more about the layout of your worksheets.


    Maurice

    • Marked as answer by zaveri cc Wednesday, November 12, 2014 9:01 PM
    Wednesday, November 12, 2014 5:53 PM
  • Hi Maurice,

    My sheet was protected so code was not working. Now it works.

    Thanks,

    Zaveri

    Wednesday, November 12, 2014 9:01 PM