Delete Cells with No Color

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hi, first I want to say thanks to all who have helped
me. I have really learned a lot form all of you. Right
now I am working on a macro that clears cells that don't
have color formatting in them anymore. It works ok if it
does not run into a cell with color. Then it doesn't
delete anything at all. How do I get it to ignore cells
with colors and keep searching and deleting those without?


Thanks again,


Todd


Sub DeleteCellswithNoColor()
For Each cell In Selection
If Selection.Interior.ColorIndex = xlNone Then
cell.ClearContents
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "No more cells to check"
End Sub
 
Todd,

Try this. Note that you were telling it to clear the contents of the
selection, not the cell.:

Sub DeleteCellswithNoColor()

Dim c As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each c In Selection
If c.Interior.ColorIndex = xlNone Then c.ClearContents
Next c

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "No more cells to check"

End Sub

hth,

Doug
 
Back
Top