Cell patterns and Cell entries

[Vickie********]

I need to know how to make this coding run differant If
for instance Part 1 (see coding Part 1) the user enters an
X (Has to be an X no other type of charator)in Cell F22 or
F23 which turns Cells F14 and F16 red. Now the user enters
a Last name in Cell F14 that cell turns back to normal
color. Then they enter a First name in Cell F16 that Cell
turns back to a normal color. If they take out the name in
either Cell that cell turns red again. If they take the X
out of Cell F22 and F23 the Cells F14 an F16 turm to
normal color.

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 _____________________CODING PART 1_________________

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

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox Target.Address
If Target.Address = "$F$14" Then
If Target <> "" Then _
Target.Interior.ColorIndex = xlNone
If Target = "" And Range("F22").Value = "X" Then _
Target.Interior.ColorIndex = 3
ElseIf Target.Address = "$F$16" Then
If Target <> "" Then _
Target.Interior.ColorIndex = xlNone
If Target = "" And Range("F23").Value = "X" Then _
Target.Interior.ColorIndex = 3
End If
If Target.Address = "$F$22" Then
If Target.Value <> "X" Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Range("F14").Interior.ColorIndex = xlNone
Else
If Range("F14").Value = "" Then
Range("F14").Interior.ColorIndex = 3
Else
Range("F14").Interior.ColorIndex = xlNone
End If
End If
End If
If Target.Address = "$F$23" Then
If Target.Value <> "X" Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Range("F16").Interior.ColorIndex = xlNone
Else
If Range("F16").Value = "" Then
Range("F16").Interior.ColorIndex = 3
Else
Range("F16").Interior.ColorIndex = xlNone
End If
End If
End If
End Sub

 

[Tom Ogilvy]

That doesn't seem to pair F22 with F14 and F23 with F16. Vickie****** aka
Kelly****** has several posts of this problem in programming and that
appeared to be the case although not well stated here.

 

[Dave Peterson]

I remember being confused reading the other posts.

 

[Tom Ogilvy]

Here is a revision that operates on F14 and F16 if there is an X in either
F22 or F23. So it should operate as you descibe.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$F$14" Or _
Target.Address = "$F$16" Then
If Target <> "" Then
Target.Interior.ColorIndex = xlNone
ElseIf Target = "" And _
UCase(Range("F22").Value) = "X" Or _
UCase(Range("F23").Value) = "X" Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = xlNone
End If
End If
If Target.Address = "$F$23" Or _
Target.Address = "$F$22" Then
If UCase(Target.Value) <> "X" Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
If Range("F22") = "" And _
Range("F23") = "" Then _
Range("F14,F16").Interior.ColorIndex = xlNone
Else
If Range("F14").Value = "" Then
Range("F14").Interior.ColorIndex = 3
Else
Range("F14").Interior.ColorIndex = xlNone
End If
If Range("F16").Value = "" Then
Range("F16").Interior.ColorIndex = 3
Else
Range("F16").Interior.ColorIndex = xlNone
End If
End If
End If
End Sub

 

[Vickie********]

That seems to work a lot better that what I had, but can
you put some comments in at points where differant things
are happening. Im trying to learn so I can understand some
of this coding stuff. Mianly what is makeing the X have to
be in that cell or the info is deleted and what is making
it be a capital. Thanks

 

[Vickie********]

Ok thats great will you help me further.
Almost same thing 2nd part. F14 and F16 (first and last
names)if both boxes are filled in then cells F22 and F23
(X) are made red if there isnt an X in one or the other of
them If there is then neither are red. If only one of the
F14 or F16 Cells are filled in, then which ever isnt it
turns red along with F22 or F23 if one or the other of
these cell doesnt have an X in it.

 

[Dave Peterson]

I thought that it should only do something if X was in the cell, but other
values were ok.

I'm not sure if this does what the OP wanted (or even requested), but I think it
hits your spec. <vvbg>

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f22,f23,f16,f14")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If Intersect(Target, Range("f22,f23")) Is Nothing Then
'do nothing here
Else
Select Case UCase(Target.Value)
Case Is = "", "X"
'do nothing
Case Else
Application.EnableEvents = False
Target.Value = ""
End Select
End If

If UCase(Range("F22").Value) = "X" _
Or UCase(Range("f23").Value) = "X" Then
With Range("f14")
If .Value = "" Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
With Range("f16")
If .Value = "" Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
Else
Range("f14,f16").Interior.ColorIndex = xlNone
End If

errHandler:
Application.EnableEvents = True

End Sub

Nope - I stand corrected - I went and looked at some of the original posts
and it looks like if either F22 or F23 has an X, then do the coloring in F14
and F16 as you have correctly implemented. You only left out the part about
restricting entries in F22 or F23 to X.