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
.
.
|