Re: Add text based on imput in another cell

Rick,
The code works - but only if there is another entry in C21. If the C21 does
not have an entry when the date is entered into C17 it does not work.
The warning will appear in C21 after any entry is added - even a space.
Also - if I enter a wrong date (pre 1980) it will enter the warning. If I
then enter the correct date (still pre 1980) it enters the warning again.
i.e. If I enter 1950 the warning appears. If I change the date to 1955 the
warning appears again.. If I change the date to after 1980 - it work great-
all warnings are removed.

Hank

"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in
message news:%23k9sqT7qIHA.3408@xxxxxxxxxxxxxxxxxxxxxxx
Give this a try and see if it does what you want. Click the tab at the
bottom of the work*** you want this functionality on and select "View
Code" from the popup menu that appears; then Copy/Paste the code below
into the code window that appeared when you did that. Now, go back to the
work*** and enter different combinations of dates in C17 and text in C21
to see if the code is doing what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Const Warning As String = " (Construction material may contain lead.)"
If Target.Address = "$C$21" And Len(Target.Value) > 0 Then
If Range("C17").Value < 1980 Then
Application.EnableEvents = False
Target.Value = Target.Value & Warning
Application.EnableEvents = True
End If
ElseIf Target.Address = "$C$17" And Len(Range("C21").Value) > 0 Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Target.Value < 1980 Then
Range("C21").Value = Range("C21").Value & Warning
End If
Application.EnableEvents = True
End If
End Sub

Rick


"HH" <deerlake@xxxxxxxxxxxxx> wrote in message
news:gOmSj.45806$%15.33243@xxxxxxxxxxxxxxxxxxxxxxxxx
Rick,
Yes, C17 and C21 are the only cells involved. What I want to do is add
the staement "Construction material may contain lead." to C21 if a year
less than 1980 is entered in C17.
You are right, there may be other text already in the cell when the
statement is to be added. Also there may be text added to C21 after the
statement is added.
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote
in message news:erlZLN6qIHA.4716@xxxxxxxxxxxxxxxxxxxxxxx
If the user can type into the cell, then you cannot put a formula in the
cell also (it will be overwritten by the user's entry). The only way to
do what you want is through an event procedure. In order to give you the
code you will need, we need some more information. Are you interested in
cells C17 and C21 only? Or is this a functionality you need across
multiple columns? If multiple columns, which ones (start column, end
column)? Always rows 17 and 21, or do other rows need to react to the
value typed into C17?

Rick


"HH" <deerlake@xxxxxxxxxxxxx> wrote in message
news:kGgSj.45664$%15.17750@xxxxxxxxxxxxxxxxxxxxxxxxx
Rick,
C21 is a general text cell where the user can type comments. There is
no formula in this cell now.

"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote
in message news:%23y4UxZ1qIHA.1736@xxxxxxxxxxxxxxxxxxxxxxx
What is in C21 now? By that I mean, does it contain a formula which
displays text (if so, tell us the formula) or does it contain text
typed in by the user?

Rick


"HH" <deerlake@xxxxxxxxxxxxx> wrote in message
news:2j9Sj.1669$_.1332@xxxxxxxxxxxxxxxxxxxxxxxxx
Maybe I was not clear enough - or maybe I don't understand. I'll
try again..

If I put a value less than 1980 in cell C17, I would like a text
statement added to cell C21. This added text statement would say
"Construction material may contain lead." There may already be a
text statement in C21 so the new statement would be added at the end
of whatever is already in the cell.
What I have come up with is: =If (C17>1980,C21="Construction material
may contain lead.","") I think this would delete whatever is
already in C21 and replace the Consturction material...statement.
But even if it would work - I don't know where to add the =if
statement.
Thanks

"HH" <deerlake@xxxxxxxxxxxxx> wrote in message
news:rx2Sj.1452$_.1022@xxxxxxxxxxxxxxxxxxxxxxxxx
Based on value in C15 I want to have a text statement added to cell
B21. I would like this to be put at the end of whatever is already
in B21 - not just replace the cell content.

Need help with the code - and where it would go.

Thanks














.