VBA Excel: Code to merge cells screws up when used with multiple sheets

Question

I'm in a situation where I have some merged cells and they do have to remain as merged cells. I'm aware of the issues that merged cells cause with VBA, but it's unavoidable in this situation.

I'm writing some code to protect all the sheets in the workbook but keep cells with a certain fill unlocked. Merged cells of course cause problems when trying to unlock the cells.

So, my code checks to see if a cell has the correct fill then checks if the cell is merged. If merged, the MergeArea address is stored in a string array and the merged cells are unmerged then unlocked.

After it's checked the relevant area of the sheet for these filled cells, I have a loop set up to re-merge all cell areas stored in the array.

This all works fine if I run it for any one sheet in the workbook (ie: without the For Each WS in ActiveWorkbook.Worksheets). However, when I add in the For Each WS loop, it starts incorrectly merging cells on the sheets.

Code is as follows:

Sub ProtectSheets()
    Application.ScreenUpdating = False
    Dim LRow As Integer, count As Integer
    Dim reMerge() As String
    Dim WS As Worksheet


    For Each WS In ActiveWorkbook.Worksheets
        count = 0
        Erase reMerge
        With WS
            LRow = .Range("h" & .Rows.count).End(xlUp).Row

            For Each c In Range("A1:AF" & LRow)
                If c.Interior.Color = RGB(253, 233, 217) Then '~~ Check for cell fill
                    If c.MergeCells = True Then '~~ Check to see if merged
                        ReDim Preserve reMerge(count)
                        reMerge(count) = c.MergeArea.Address '~~Store mergearea address in array
                        count = count + 1
                        c.MergeCells = False
                    End If
                    c.Locked = False
                End If
            Next c

            For i = LBound(reMerge) To UBound(reMerge)  '~~ Remerge all cells that were originally merged
                .Range(reMerge(i)).Merge
            Next i

        End With
        WS.Protect Password:="xxxx", userinterfaceonly:=True
    Next WS
    Application.ScreenUpdating = True
End Sub

Try qualifying For Each c In Range("A1:AF" & LRow) as For Each c In .Range("A1:AF" & LRow) — David Zemens
@DavidZemens, thank you very much sir. What a silly thing to miss. That solves. Can you post as an answer so that I can accept? Cheers — Sam WB
Sure thing! Sometimes another set of eyes is all it takes :) — David Zemens

David Zemens David Zemens · Accepted Answer · 2013-04-29T14:10:23

Try qualifying For Each c In Range("A1:AF" & LRow) as For Each c In .Range("A1:AF" & LRow)

Explanation, for those who might face a similar problem in the future:

Without qualifying the Range argument, Excel assumes you refer to a range object on the ActiveSheet. Because you are working inside a With block (With WS), adding the . qualifies this Range as an object on the WS worksheet, for each iteration of the loop.

If you were not working inside a With block, you would need to fully qualify the Range object, e.g., WS.Range("A1") or Sheets(1).Range("B52"), etc.

VBA Excel: Code to merge cells screws up when used with multiple sheets

1 Answers