| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter text in cell Code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter text in cell Code
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) -- sb "Kelly" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I dont understand
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 -----Original Message----- 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) -- sb "Kelly" wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I dont understand
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). -- sb "Kelly" wrote in message ... 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 -----Original Message----- 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) -- sb "Kelly" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter text in cell Code
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. Kelly wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Im starting to relax a little
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 -----Original Message----- 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). -- sb "Kelly" wrote in message ... 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 -----Original Message----- 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) -- sb "Kelly" wrote in message ... 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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter Help text for a cell | Excel Worksheet Functions | |||
| Automatically enter cell text | Excel Discussion (Misc queries) | |||
| Enter text in a cell to return a text value in same cell | Excel Discussion (Misc queries) | |||
| What is the Chr() code for Alt+Enter in a cell when entering text? | Excel Discussion (Misc queries) | |||
| Need VBA code to enter text into a textbox !!!! | Excel Discussion (Misc queries) | |||
![Kelly[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
