Enter text in cell Code

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

This is what Ive got to work with. Insted of where the X
is I need that to be any text for example a name. If the
text or name is enter the other cell turns red If the text
name is removed from the cell it turns back to white.

If Target.Address = "$F$14" Or Target.Address = "$F$16"
Then
If UCase(Target) = "X" Then
If Len(Range("F22")) = 0 Then
Range("F22").Interior.ColorIndex = 3

End If
End If
End If

If Target.Address = "$F$14" Or Target.Address
= "$F$16" Then
If UCase(Target) = "X" Then
If Len(Range("F23")) = 0 Then
Range("F23").Interior.ColorIndex = 3
End If
End If
End If
 
Kelly,

You can do this in one of two ways

Dim nm as String
nm = Range(xxxxx) ' set to range where name is

than replace "X" with nm (no quotes)

or
Dim nm As String
nm = InputBox("Enter name","Name")
' trap for error
If Len(nm) = O then
MsgBox("You didn't enter a name",vbOKOnly)
exit sub
End If
than replace "X" with nm (no quotes)
 
I dont understand what you mean by set the name as range
on example 1.
I get a debug and the nm= range (xxxxx) is high lighted.
heres what I did
Also thanks for your time steve its been helpfull

Dim nm As String
nm = Range(xxxxx)
Rem ' set to range where name is

Rem than replace "X" with nm (no quotes)

If Target.Address = "$F$14" Or Target.Address
= "$F$16" Then
If UCase(Target) = nm Then
If Len(Range("F22")) = 0 Then
Range("F22").Interior.ColorIndex = 3

End If
End If
End If

If Target.Address = "$F$14" Or Target.Address
= "$F$16" Then
If UCase(Target) = nm Then
If Len(Range("F23")) = 0 Then
Range("F23").Interior.ColorIndex = 3
End If
End If
End If
 
Kelly,

Guess I should have been clearer.

Let's say that the name you are looking for is in cell A1

than
nm = Range("A1")
or
nm = Range("A1").Text

forget about the string of xxxx's (my sloppiness).
 
If the number of colors doesn't exceed 3, you may want to look at
Format|Conditional Formatting.

You get up to 3 different formats (plus the normal) if you use this.
 
OK here what I got it works kind of Thanks for all your
help. You've been a life saver. I am sitting a little more
relaxed at this point anyway. I'm sure they'll want
something different.
I do notice things I wish were different but I don't have
the other data that goes along with this so I don't know
exactly what they will find. I do need F22 and F23 to only
except an X. At this point it only triggers the red if you
enter an X but the cells do let you type in anything and
that will be a problem. Then Id like to protect or hide
your code some how so people don't mess with it. So if you
have a minute or two I would be most great full.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Rem Put an X in cells F22 or F23 makes Cells F14 AND F16
red
Rem _____________________AREA WORKS_____________________
Rem YOU CAN ENTER ANY TEXT, BUT ONLY AN X TRIGGERS THE RED

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "X" Then
If Len(Range("F14")) = 0 Then
Range("F14").Interior.ColorIndex = 3
End If
End If
End If

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "X" Then
If Len(Range("F16")) = 0 Then
Range("F16").Interior.ColorIndex = 3
End If
End If
End If

Rem Take out the X in cells F22 and F23 makes Cells F14
and F16 white
Rem _______________________AREA WORKS__________________

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "" Then
If Len(Range("F14")) = 0 Then
Range("F14").Interior.ColorIndex = 0
End If
End If
End If

If Target.Address = "$F$22" Or Target.Address
= "$F$23" Then
If UCase(Target) = "" Then
If Len(Range("F16")) = 0 Then
Range("F16").Interior.ColorIndex = 0
End If
End If
End If

Rem Enter Text in F14 or F16 turns cells F22 and F23 red
Rem ______________________Area Works______________________

Dim ln As String

ln = Range("F14").Text
If Range("F14,F16") = (ln) Then
Range("F22,F23").Interior.ColorIndex = 3
End If

Rem Remove Text in F14 or F16 turns cells F22 and F23 white
Rem ______________________Area Works______________________

Dim fn As String

fn = Range("F16").Text
If Range("F14,F16") = (fn) Then
Range("F22,F23").Interior.ColorIndex = 0
End If

End Sub
 
Back
Top