Macro - Cell clearing
 

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

Macro - Cell clearing
 

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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all yellow cells
in
a worksheet?

Macro - Cell clearing
 

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



"Paul H" wrote:

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

Macro - Cell clearing
 

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

"Paul H" wrote:

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

Macro - Cell clearing
 

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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all yellow cells
in
a worksheet?

Macro - Cell clearing
 

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all yellow
cells
in
a worksheet?

Macro - Cell clearing
 

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" wrote:

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all yellow
cells
in
a worksheet?

Macro - Cell clearing
 

Paul, try this one

Sub Clear_Yellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, 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

"Paul H" wrote in message
...
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" wrote:

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all yellow
cells
in
a worksheet?

Macro - Cell clearing
 

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(xlCellTypeConst ants,
2)
If cell.Interior.ColorIndex = 36 Then
cell.Formula = ""
End If
Next cell

End Sub

"Paul B" wrote:

Paul, try this one

Sub Clear_Yellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, 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

"Paul H" wrote in message
...
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" wrote:

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all yellow
cells
in
a worksheet?

Macro - Cell clearing
 

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(xlCellTypeConst ants, 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


"Paul H" wrote in message
...
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(xlCellTypeConst ants,
2)
If cell.Interior.ColorIndex = 36 Then
cell.Formula = ""
End If
Next cell

End Sub

"Paul B" wrote:

Paul, try this one

Sub Clear_Yellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants,
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

"Paul H" wrote in message
...
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" wrote:

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all
yellow
cells
in
a worksheet?

Macro - Cell clearing
 

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" wrote:

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(xlCellTypeConst ants, 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


"Paul H" wrote in message
...
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(xlCellTypeConst ants,
2)
If cell.Interior.ColorIndex = 36 Then
cell.Formula = ""
End If
Next cell

End Sub

"Paul B" wrote:

Paul, try this one

Sub Clear_Yellow()
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants,
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

"Paul H" wrote in message
...
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" wrote:

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all
yellow
cells
in
a worksheet?

Macro - Cell clearing
 

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

"Paul H" wrote in message
...
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" wrote:

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(xlCellTypeConst ants,
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


"Paul H" wrote in message
...
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(xlCellTypeConst ants,
2)
If cell.Interior.ColorIndex = 36 Then
cell.Formula = ""
End If
Next cell

End Sub

"Paul B" wrote:

Paul, try this one

Sub Clear_Yellow()
On Error Resume Next
For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants,
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

"Paul H" wrote in message
...
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" wrote:

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


"Paul H" wrote in message
...
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

"Bob Phillips" wrote:


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)


"Paul H" wrote in message
...
Can a macro be written that would clear the contents from all
yellow
cells
in
a worksheet?