Excel VBA: Clear all cells with a certain interior color 3

[artsandcraftshome]

I have a spreadsheet that has all cells requiring operator entry colored yellow. I'd like to create a macro/button combination that will clear only those cells when clicked, but preserve the data in all other cells. Do I need to protect all other cells and just "clear unprotected cells"?
thanks
John

 

[xlbo]

You can do it that way or:
const Light_Yellow = 36
With Sheets("Sheetname&quot
For Each c In .Range("D2:E181&quot'change to be your range
If c.Interior.ColorIndex = Light_Yellow Then
c.ClearContents
Else
End If
Next
End With

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[SkipVought]

John,

I'd go a step further. I would...

1. select all the cells in the range that users will enter data in
2. name the range something like "DataEntryRange"

Then you can use this range name to do lots of things like...

1. Clear data

[DataEntryRange].ClearContents

2. change the interior color

[DataEntryRange].Interiro.Colorindex = 25

3. protect the sheet by locking all cells except the data entry cells

Cells.Locked = True
[DataEntryRange].Locked = False
ActiveSheet.Protect

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Nice and simple, Skip! You are a Named Range guru. . . heck, I'LL give you a star for that.

VBAjedi

 

[sfvb]

I like named ranges as much as the next guy, but I prefer xlbo's way. But what the heck, both where good answers. Barkeep, stars all around!

 

[krinid]

Skip,
have you considered writing a FAQ for your knowledge of name ranges? Above could be the base, and add any other common/useful uses you can think of. I think many users would find it beneficial.