Worksheet Change help please
 

I need to be able to do the following, if possible:

Columns P:T have drop down validation boxes in them, if the user selects
"Cr" from the list in any of these 5 columns and the value in column B is 1
then I want "Yes" entered into column V.
Also, if the value selected is not "Cr" and column B is 1 then I would like
"No" entered in column V.

This is what I was trying, but with no luck:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Or Target.Column <= 20 Then
If Target.Value = "Cr" And '(not sure how to reference column B and
target row).Value = 1 Then
'(not sure how to reference column V on target row).Value = "Yes"
ElseIf Target.Value < "Cr" And '(not sure how to reference column B and
target row).Value = 1 Then
'(not sure how to reference column V on target row).Value = "No"
End If
End If
End Sub

Many thanks in advance.

Gareth

Worksheet Change help please
 

What about the other choices

- target = "Cr" and B <1
- target < "Cr" and B = 1

Private Sub Worksheet_Change(ByVal target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With target
If .Column = 16 And .Column <= 20 Then
If .Value = "Cr" And Me.Cells(.Row, "B").Value = 1 Then
Me.Cells(.Row, "V").Value = "Yes"
ElseIf .Value < "Cr" And Me.Cells(.Row, "B").Value < 1 Then
Me.Cells(.Row, "V").Value = "No"
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gareth" wrote in message
...
I need to be able to do the following, if possible:

Columns P:T have drop down validation boxes in them, if the user selects
"Cr" from the list in any of these 5 columns and the value in column B is
1
then I want "Yes" entered into column V.
Also, if the value selected is not "Cr" and column B is 1 then I would
like
"No" entered in column V.

This is what I was trying, but with no luck:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Or Target.Column <= 20 Then
If Target.Value = "Cr" And '(not sure how to reference column B and
target row).Value = 1 Then
'(not sure how to reference column V on target row).Value = "Yes"
ElseIf Target.Value < "Cr" And '(not sure how to reference column B
and
target row).Value = 1 Then
'(not sure how to reference column V on target row).Value = "No"
End If
End If
End Sub

Many thanks in advance.

Gareth

Worksheet Change help please
 

No need for VBA. Addressing your specific question, in V2 enter the
lightly tested formula
=IF(OR(AND(COUNTIF(P2:T2,"Cr")=1,B2=1),AND(COUNTI F(P2:T2,"Cr")=0,B2<
1)),"Yes","No")

I made the assumption that you want the opposite result for conditions
outside of those explicitly stated by you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I need to be able to do the following, if possible:

Columns P:T have drop down validation boxes in them, if the user selects
"Cr" from the list in any of these 5 columns and the value in column B is 1
then I want "Yes" entered into column V.
Also, if the value selected is not "Cr" and column B is 1 then I would like
"No" entered in column V.

This is what I was trying, but with no luck:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Or Target.Column <= 20 Then
If Target.Value = "Cr" And '(not sure how to reference column B and
target row).Value = 1 Then
'(not sure how to reference column V on target row).Value = "Yes"
ElseIf Target.Value < "Cr" And '(not sure how to reference column B and
target row).Value = 1 Then
'(not sure how to reference column V on target row).Value = "No"
End If
End If
End Sub

Many thanks in advance.

Gareth