display "Missing" when blank

  • Thread starter Thread starter nada
  • Start date Start date
N

nada

hello,
how could i make any blank cell in a particular range in a worksheet
displays certain word if it is dataless??
 
Past this formula in B2

=IF(A2="","missing","")


write something in A2 and then delete it.
 
Hi

What do you mean by blank?

Does the cell hold a formula or is the user supposed to to enter data into
the cell?

Regards,

Per
 
that's not what i need to do, i'll try to be clearer
i have some data in the range b6:m19, some cells in this range are blank, so
i want them automatically display the word "missing" if there is no data in
them, thank you.
 
hello,
i have some data in the range b6:m19, some cells in this range are blank, so
i want them automatically display the word "missing" if there is no data in
them, thank you.
 
Hello,

Here's a way to do it.

Copy the code below, right click on the sheet tab where you this function.
Click View Code. Paste the code on the code sheet and close the code editor.

Deactivate the sheet, and then activate it again. All "empty" cells will now
say "Missing"
User can enter data as usual and if he delete data in target range the cell
will say "Missing"

I hope this is what you need.

Private Sub Worksheet_Activate()
Dim MyRange As Range
Set MyRange = Range("B6:M19")
For Each c In MyRange
If c.Value = "" Then c.Value = "Missing"
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("B6:M19")
Set isect = Application.Intersect(MyRange, Target)
If Not isect Is Nothing Then
If Target.Value = "" Then Target = "Missing"
End If
End Sub

Best regards,
Per
 
Back
Top