Clear Shaded Cells

[Ed Davis]

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.

 

[Bernard Liengme]

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

 

[Dave Peterson]

Or just get the entire range at once:

ActiveSheet.UsedRange.Interior.ColorIndex = xlNone

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

 

[Ed Davis]

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]

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection 'ActiveSheet.usedrange

For Each myCell In myRng.Cells
If myCell.Interior.ColorIndex = 3 Then
myCell.ClearContents
End If
Next myCell

End Sub

Record a macro when you change the colorindex of a cell. Then use that in place
of the 3.

Select the range you want before you run the code. Or use the UsedRange if you
want.

 

[Gord Dibben]

For Each mycell In ActiveSheet.UsedRange
If mycell.Interior.ColorIndex <> xlNone Then
mycell.ClearContents
End If
Next mycell


Gord Dibben MS Excel MVP

 

[Ed Davis]

This is what I get when I record the macro:
I tried to use xlAutomatic Did not work
I tried to use 3 did not work
Then I tried to use 0.599993896298105 and that did not work either.
I even tried to use the 0 and that did not work either.



Range("B17").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With

 

[Ed Davis]

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

 

[Ed Davis]

I get the following error
cannot clear part of a merged cell.
I only had 1 merged cell that was shaded and even after removing the merge
and the shade I still get the same error.
Can anyone tell me if there is a way around this.

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

 

[Dave Peterson]

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

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

 

[Ed Davis]

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

 

[Gord Dibben]

Did you select a range before running the macro?

If you don't want to select a range go back to Activesheet.usedrange instead
of selection.


Gord

 

[Ed Davis]

I got it to work using this suggestion.
Thank you very much.

 

[Gord Dibben]

Good to hear.


Gord

I got it to work using this suggestion.
Thank you very much.