Replace values in shaded cells with blanks

[sue]

Hi everyone,
I've been given an excel spreadsheet that has all the invalid values
colour coded.
eg red cells if the value is too small, green if too big, yellow if
subject moved during reading.
etc- so some are numeric and some not.

To transfer the data to a statistical package I need to replace all
values in shaded cells with blanks.
Any suggestions?

Thanks in advance
Excel Ignoramus
sue

 

[Guest]

Hi sue:

Enter and run this smal macro:

Sub marine()
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = xlNone Then
Else
r.Value = ""
End If
Next
End Sub

 

[Guest]

Sue,

This one should work.

Sub clearcells()
'clears cells from the active sheet
Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.Interior.ColorIndex <> xlColorIndexNone Then
rCell.Clear
End If
Next rCell
End Sub

 

[sue]

Dear groupies(?),
Just back from holidays and tried your macros .
Worked beautifully. Even managed to adjust macro to do it for every
sheet in my work book.
Gave you an excellent rating.
Many thanks and happy new year.

sue