Hi Dave
I copied and pasted this and absolutely nothing happens no errors and
nothing changed on the sheet
Thanks for your help Dave.


Dim myCell As Range
Dim myRng As Range

Set myRng = Selection 'ActiveSheet.usedrange
For Each myCell In myRng.Cells
If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then
myCell.Value = ""
End If
Next myCell

"Dave Peterson" wrote in message
...
Dim myCell As Range
Dim myRng As Range

Set myRng = Selection 'ActiveSheet.usedrange
For Each myCell In myRng.Cells
If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then
myCell.Value = ""
End If
Next myCell

Ed Davis wrote:

I get cannot clear part of a merged cell. with this code

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
For Each mycell In ActiveSheet.UsedRange
If mycell.Interior.ColorIndex < xlNone Then
mycell.ClearContents
End If
Next mycell


Gord Dibben MS Excel MVP

On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis"
wrote:

Thank you for the quick response.
I was looking to only clear the data not the shade as the shaded areas
show
the user where the data goes.


"Dave Peterson" wrote in message
...
Or just get the entire range at once:

ActiveSheet.UsedRange.Interior.ColorIndex = xlNone

or even do all the cells:
activesheet.cells.Interior.ColorIndex = xlNone

Bernard Liengme wrote:

Sub tryme()
For Each mycell In ActiveSheet.UsedRange
mycell.Interior.ColorIndex = xlNone
Next mycell
End Sub

If you are new to VBA:
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ed Davis" wrote in message
...
Is there a way to clear all shaded cells in a worksheet using a
macro?

I have received a lot of help from this news group.
Thank you very much.


--

Dave Peterson


--

Dave Peterson