Macro - Cell clearing

[Guest]

Can a macro be written that would clear the contents from all yellow cells in
a worksheet?

 

[Bob Phillips]

For Each cell In Activesheet.UsedRange
If cell.Interior.ColorIndex = 6 Then
cell.ClearContents
End If
Next cell

--

HTH

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

 

[Guest]

Oh, the heck with it...Yes...

Sub test()
Dim cell As Range
For Each cell In Selection
If cell.Interior.ColorIndex = 6 Then
cell.ClearContents
End If
Next cell
End Sub

6 is bright yellow
36 is light yellow

or using Range Names...
Sub test()
Dim cell As Range
For Each cell In Range("TEST")
If cell.Interior.ColorIndex = 6 Then
cell.ClearContents
End If
Next cell
End Sub

 

[Guest]

Paul,
Yes...See my answer to your original question.
Gary Brown

 

[Guest]

I tried inserting the formula you listed. The debug pointed to an erroe in
the ' markjust before your formula. What should i do. 678-256-1968
Thanks

 

[Paul B]

Paul, it could be that you have merged cells in your sheet, if that is the
case, just change one line in Bob's code, like this

Sub Clear_Yellow()
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 6 Then
cell.Formula = ""
End If
Next cell

End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

I tried it and it worked then subsequent clearings didn't work. Additionally
I don't want to clear the formulas just the contents.

Paul

 

[Paul B]

Paul, try this one

Sub Clear_Yellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 2)
If cell.Interior.ColorIndex = 6 Then
cell.Formula = ""
End If
Next cell

End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

I inserted your suggestion and I get an error ot the last ' mark? I marked it
below. What does this mean. Additionally the name of the macro at the top is
now yellow with an arrow pointing to it in the left margin.

Thanks

Paul

Sub Clear_Yellow()
'
' Clear_Yellow Macro
' Macro recorded 1/7/2005 by hargrap
'

' <Here
Sub ClearYellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
2)
If cell.Interior.ColorIndex = 36 Then
cell.Formula = ""
End If
Next cell

End Sub

 

[Paul B]

Paul, looks like you have 2 macro parts in what you posted, Sub
Clear_Yellow() and Sub ClearYellow()

delete this part

Sub Clear_Yellow()
'
' Clear_Yellow Macro
' Macro recorded 1/7/2005 by hargrap
'

Are start over and delete all and copy and past the code below

Sub Clear_Yellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 2)
If cell.Interior.ColorIndex = 6 Then
cell.Formula = ""
End If
Next cell

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Still can't get it to work!
If you like i can send you the file for your review.

Thanks again for your interest.

Paul

 

[Paul B]

Paul, I will take a look at it, send it to paul_batsurfbestdotnet replace
at and dot with @ .
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003