Replace values in shaded cells with blanks
 

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

Replace values in shaded cells with blanks
 

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
--
Gary's Student


"sue" wrote:

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

Replace values in shaded cells with blanks
 

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

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"sue" wrote:

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

Replace values in shaded cells with blanks
 

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