Clear the contents of Unprotected cells - EXCEPT a certain range

Hi guys,

I'm looking to create a Macro I can assign to button that will clear the contents of all Unprotected cells in my sheet EXCEPT a specific range of cells (that are also unprotected).

The worksheet is filled out daily, so most of it needs to be cleared and re-entered. However there are a few blocks of cells that change so infrequently that the it isn't efficient for the end user to have to re-enter daily, but they still need access to change them when necessary.

I am using the below code which takes care of the clearing all the unprotected cells. I'm hoping there is an amendment that can be made for it to EXCLUDE the block of cells I'm referring to.

Please Login or Register  to view this content.

Thanks!

Theoretically,you could exclude the intersection of your special ranges and the range returned by the FindFormat.

What are your special ranges?

One way of doing this without having to hard code the specific range of cells in VBA is to use formatting - i.e. give the cells you don't want cleared a different background colour.

The code below will clear all unlocked cells that don't have a pale blue background colour.

Please Login or Register  to view this content.

To chose your own colour, in Excel select a cell and chose the background colour you want. Then, in the VBA Immediate Window (View -> Immediate Window) type

Please Login or Register  to view this content.

It will return a number that you can use for SkipColour.

Thank you mgs73 for the reply. I was able to find the number for the color of the cells, but get a compile error when entering the code below.


Public Const SkipColour As Long = 13158083

Public Sub Test()
ClearCells ActiveSheet
End Sub

Public Sub ClearCells(sh As Worksheet)
Dim Cell As Range

For Each Cell In sh.UsedRange
If Not Cell.Locked And Not Cell.Interior.Color = SkipColour Then Cell.ClearContents
Next
End Sub



The error states that "constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules."

Any idea as to what went wrong?

Just for future reference, I've figured it out.

If you are looking to clear the contents from all unprotected cells (merged cells included) with the exception of a select few cells, the below code works Beautifully!

Please Login or Register  to view this content.

Where 13158083 can be replaced with the cell color of choice.

To copy what mgs73 posted...

To chose your own colour, in Excel select a cell and chose the background colour you want. Then, in the VBA Immediate Window (View -> Immediate Window) type

Please Login or Register  to view this content.

Thanks for all who helped out. I hope this can help others too.