Color detection

[Dan wilson]

Good day. I am using Excel 2002 with Windows ME. I have
created a worksheet for ordering products. Each of the
cells that needs to be cleared to start a new order is
formatted with a green pattern. These are the only cells
that the user will be allowed to modify when the project
is done. Is there a way to write a macro that will clear
only the green cells on the worksheet?

Thanks, Danno...

 

[Gord Dibben]

Dan

Assuming the uncolored cells are locked and sheet protection enabled, you can
run this macro to clear the unlocked(colored) cells.

Sub UnLocked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
Cells.Select
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not Cell.Locked Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If
tempR.Select
tempR.ClearContents
End Sub


Gord Dibben Excel MVP

 

[Bob Phillips]

This code loops thorugh the usedrange and identifies the green cells

For Each cell In Activesheet.UsedRange
If cell.Interior.Colorindex = 10 Then
cell.Clearcontents
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dan Wilson]

Good day Gord and Bob,

Thanks for the quick response. I can always count on
getting answers from this newsgroup. I will try both
methods suggested.

Thanks, Danno...