Clear cell contents based on color?

Question

Is it possible to clear a large number of cells contents based on color alone? I don't think that simply filtering is going to work well on this because the dataset is large and 'wide'

Answer 1

If you don't need this done programatically, it can be faster to just use Find and Replace (Ctrl-H).

1. Press Ctrl-H
2. Click Options > >
3. Click the top Format... button to search for for colored cells (use the Fill tab)
4. Leave the Replace with field blank to delete the contents of cells with the format you specified.

Answer 2

This will clear the content of any cell within the range A1:G8 filled with yellow (65535). Change the color for your color and the range for your range. This is kind of crude, sorry.

Sub Macro1()

    Range("A1:G8").Select
    For Each Cell In Selection
        If Cell.Interior.Color = Excel.XlRgbColor.rgbYellow Then
            Cell.Clear
        End If
    Next

End Sub