Clear contents of cell range with merged cells based on Interior.Color

Question

I have some VBA to clear cells in a specified range based on the colour of the cell. These cells are hard-coloured. It works without unmerged cells, yet as soon as I add a merged cell inside the range it fails.

I've been doing some digging, and it seems to be that I need to use Cells to reference the first cell in the merge. As I'm not familiar with VBA, I'm not sure how to incorporate the two lots of code.

Any help, advice, or links to other solutions you can provide would be greatly appreciated.

Current code that works:

Sub ClearContentByBackground()
For Each Cell In Range("b1:i32")
    If Cell.Interior.Color = RGB(226, 239, 218) Then
        Cell.ClearContents
    End If
Next
End Sub

Code that I found during my search:

If Cells(j, l).MergeCells Then
    Cells(j, l).MergeArea.ClearContents
Else
    Cells(j, l).ClearContents
End If

Алексей Р Алексей Р · Accepted Answer · 2021-06-01T12:27:53

Try this:

Option Explicit

Sub ClearContentByBackground()
    Dim cell As Range
    For Each cell In Range("b1:i32")
        If cell.Interior.Color = RGB(226, 239, 218) Then
            cell.MergeArea.ClearContents
        End If
    Next
End Sub

If the cell is part of a merged range, you must clear the contents of the MergedArea. If the cell is not part of the merged range, then the contents of one cell must be cleared. Since the cell.MergeArea property of such a cell is equal to the cell itself, the condition If Cells(j, l).MergeCells Then is redundant here.

Clear contents of cell range with merged cells based on Interior.Color

1 Answers