Replace values in shaded cells with blanks

  • Thread starter Thread starter sue
  • Start date Start date
S

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
 
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
 
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
 
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
 
Back
Top