If then

[trebor1956]

Hi,

I have col A with dropdown list (Pass or Fail) and col B with dropdown list (Y or N). If A2 has Fail and B2 has Y then I would like A2 entry changed to Pass. I seem to be going round in circles, hope you can help.

Thank you.

 

[6StringJazzer]

What you are describing requires a macro. You can enter a value in column A (Pass or Fail), or you can have a formula =IF(B2="Y","Pass") but you can't have both at the same time.

Do you have this situation only in row 2, or is it repeated in many rows?

 

[trebor1956]

Its repeated on many rows. The idea was that if a check fails - then "Fail" is selected from the dropdown in A2 then when it is fixed "Y" is selected from dropdown in B2 - when "Y" has been selected to say the issue is fixed then I was trying to find a way to automatically change A2 to "Pass".

 

[6StringJazzer]

You can use this code in the module for the sheet containing the data you described. If you don't have experience with VBA I can give more details on how to do this.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range

   For Each Cell In Target
      If Target.Column = 2 And Target = "Y" Then ' column B
         Cells(Target.Row, "A") = "Pass"
      End If
   Next Cell

End Sub

 

[trebor1956]

Hi,
Thank you for the VBA code. I have put it into the s/sheet but nothing happens. Am I correct in assuming that Target.column = 2 (where 2 is column B)?

 

[6StringJazzer]

I was a bit hasty. The Change event will not be triggered by a selection from a Data Validation dropdown. Let me take another look.

 

[Fluff]

The Change event will not be triggered by a selection from a Data Validation dropdown

It does for me.

@trebor1956
If you right click the tab of the sheet you want this to work on & select "View code", can you see the code in the window that opens?

 

[6StringJazzer]

It does for me.

That's odd. I put my code in a file and the Sub is never invoked when I select a Y or N from a data validation dropdown list.

 

[Fluff]

That's definitely odd, change events have always worked for me with data validation.

 

[trebor1956]

Hi,
I put your code as follows;
Alt + F11
then copied your code into 'this workbook'