Conditional formatting or formula to automatically delete cell contents

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Papa - 05 Dec 2007 23:36 GMT

I am currently entering production details for an order but until
there are actually sufficient components available. the order remains
"on hold". I leave the cells unshaded ( i.e. no colour fill) to
indicate that the order is not yet in production. Shortfall items are
listed in cells at the end of the row.
The challenge! I would like to have these shortfall details deleted
automatically once the main order details are shaded (manually). The
manual shading is done when the order is actually put into production
( i.e.there are enough materials available). Any suggestions from the
excel gurus out there??

Reply to this Message

Gord Dibben - 06 Dec 2007 00:26 GMT

Formulas cannot delete anything.

They only return results.

You could have the shortfall details subtracted from a total of items but that's
about it using formulas.

To actually delete values from those cells based on a manually colored cell
would take VBA.

Event code would be the best way to go but a color change is not an event that
would trigger code.

Other than the color change is there anything that Excel could take as a trigger
when the order is put into production?

Here is a macro you could run manually when the order is put into production.

Assumes the colored cell is A10 and red.

B10:F10 is where your "shorts" are located.

Sub delete_shorts()
With ActiveSheet
If .Range("A10").Interior.ColorIndex = 3 Then
.Range("B10:F10").ClearContents
End If
End With
End Sub

More detail could bring more help.

Gord Dibben MS Excel MVP

>I am currently entering production details for an order but until
>there are actually sufficient components available. the order remains
[quoted text clipped - 6 lines]
>( i.e.there are enough materials available). Any suggestions from the
>excel gurus out there??

Reply to this Message