Deleting cells based on contents or displayed contents

Hi there,

I have a spreadsheet that I'll be passing on to other people who know nothing of excel, so I need to simplify it as much as possible, so I'm writing Macros to make it a simple button-press to formulate reports. I need to apply a formula to a column but it is of variable length, so what my plan to do is apply the fomula using an if function, where it would return a specific value if it reached the end of the table. For example, in column E i input the formula:

=IF(ISBLANK(D56),"purple",SUMIFS('Raw Data'!H:H,'Raw Data'!C:C,Information!B56,'Raw Data'!A:A,Information!A56))

I did this with the plan of doing a find and replace, finding "purple" and replacing it with nothing, hoping that would delete the cell contents, but it simply removed the word purple from the formula. Is there any way I can make it so I can include a macro that adds a variable to a field of variable length, or, more simply, delete cells based on criteria?

Thanks for any help.