Conditional formatting or formula to automatically delete cellcontents

  • Thread starter Thread starter Papa
  • Start date Start date
P

Papa

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