Clear Shaded cells

[Chirag]

The following macro should help you get started:

Sub ClearCellsIfColorMatches(ByVal Sht As Worksheet, _
ByVal InteriorColor As Long)

Dim R As Long
Dim C As Long

With Sht.UsedRange
For R = .Row To .Row + .Rows.Count - 1
For C = .Column To .Column + .Columns.Count - 1
If Sht.Cells(R, C).Interior.Color = InteriorColor Then
Sht.Cells(R, C).Clear
End If
Next
Next
End With
End Sub

Pass it the worksheet that you want to process and the color value in RGB
format.

- Chirag

PowerShow - View multiple PowerPoint slide shows simultaneously
http://officeone.mvps.org/powershow/powershow.html

 

[Microsoft Communities]

I would like to use a macro to clear all cells in a sheet with the shade #
of 35. Can someone please tell me how to do this.

 

[Don Guillett]

Since you failed to say HOW the color got there, try this.

Sub ClearColorAndCF()
On Error Resume Next
For Each c In Selection
If c.FormatConditions(1).Interior.ColorIndex = 35 Then
c.FormatConditions(1).Interior.ColorIndex = 0
If c.Interior.ColorIndex = 35 Then c.Interior.ColorIndex = 0
End If
next
End Sub

 

[Microsoft Communities]

This clears the interior shade I want to clear the data not the shade.

 

[Microsoft Communities]

This clears the interior shade I want to clear the data not the shade.

 

[Don Guillett]

You still didn't say how the shade got there in the first place.
Sub ClearColorAndCF()
On Error Resume Next
For Each c In Selection
If c.FormatConditions(1).Interior.ColorIndex = 35 Then
c.clearcontents
'c.FormatConditions(1).Interior.ColorIndex = 0
If c.Interior.ColorIndex = 35 Then c.clearcontents
'If c.Interior.ColorIndex = 35 Then c.Interior.ColorIndex = 0

End If
next
End Sub

 

[Don Guillett]

Are you refusing to tell us how the shading got there?
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Microsoft Communities]

The shade is there to let users know where the data should go.
I only want to clear the data not the shade. This sheet is used for data
input and I have macros do other things with the data.
So I would like to run a macro to setup the sheet without data in the shaded
areas. The is other information on the sheet that I want the data to stay.
Just get rid of the data in the shaded areas.

 

[Rick Rothstein]

There are two ways to "shade" a cell... either conditional formatting or
directly filling the interior... each requires its own method of finding the
color, Don was asking how did the color get into the cell so we would know
what to recommend to you.