If a specified cell has any text entered in it, this cell will ent

  • Thread starter Thread starter Sara Wiseman
  • Start date Start date
S

Sara Wiseman

If I want a cell to check to see if another cell has text in it, and then if
it does have text then it enters the date, what formula would I use? I have
part of it figured out:

=IF(ISTEXT(B2), "", "")

What I am missing is: =IF(ISTEXT(B2), "how do I get it to enter the date
here?", "")

I cannot figure out what goes inbetween the fist set of " to get it to enter
today's date. Please help me if possible, I'm going NUTS!. Thank you.

Sara
 
Sara,

You need to use an event - either the change or calculate event will work. Copy the code below,
right-click the sheet tab, select "View Code" and paste the code into the window that appears. It
will put the date next to any text entry in column B - the code uses the change event, so it will
work when you enter a value into a single cell in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDbl As Double
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
On Error GoTo StringEntry
myDbl = CDbl(Target.Value)
GoTo notString
StringEntry:
Application.EnableEvents = False
With Cells(Target.Row, Target.Column + 1)
.Value = Date
.NumberFormat = "mmm dd, yyyy"
End With
Application.EnableEvents = True
Exit Sub
notString:
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top