I am trying to make an if statement that accomplishes something like, If b1=3 then a2=a1 else pick choice from the data validation list(drop down list) that is from d1 to d5. Thanks

The only way I can think of to make it automatic is to monitor B1 with a worksheet change macro as shown below. For a slighly less automatic method, you could include an =A1 in your list, so the user could choose that value when B1 = 3. Anyway, here's the code: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$B$1" Then 'If B1 = 3, delete A2 validation list and set A2 = A1 If Target = 3 Then With Range("A2").Validation .Delete End With Range("A2") = Range("A1") 'Else clear A2 and add Data Validation list. Else Range("A2") = "" With Range("A2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$D$1:$D$5" .IgnoreBlank = True .InCellDropdown = True End With Range("A2").Activate End If End If Application.EnableEvents = True End Sub

THANKS IT WORKED GREAT

But now I realized a problem when appling to the actual use. I am trying to use the above code around 164 times with slight variations each time, but only the first use is responsive on the sheet An example of what I am trying to accomplish is: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$B$1" Then 'If B1 = 3, delete A2 validation list and set A2 = A1 If Target = 3 Then With Range("A2").Validation .Delete End With Range("A2") = Range("A1") 'Else clear A2 and add Data Validation list. Else Range("A2") = "" With Range("A2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$D$1:$D$5" .IgnoreBlank = True .InCellDropdown = True End With Range("A2").Activate End If End If Application.EnableEvents = True End Sub Private Sub Worksheet_Change2(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$B$2" Then 'If B2 = 3, delete A3 validation list and set A3 = A2 If Target = 3 Then With Range("A3").Validation .Delete End With Range("A3") = Range("A2") 'Else clear A3 and add Data Validation list. Else Range("A3") = "" With Range("A3").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$D$1:$D$5" .IgnoreBlank = True .InCellDropdown = True End With Range("A3").Activate End If End If Application.EnableEvents = True End Sub

You can only have one Worksheet_Change macro per sheet, but that macro can do different things depending on the change. For example, Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$B$1" Then 'Do whatever you need to do if the change occurred in B1 End If If Target.Address = "$B$2" Then 'Do whatever you need to do if the change occurred in B2 End If If Target.Address = "$B$3" Then 'Do whatever you need to do if the change occurred in B3 End If Application.EnableEvents = True End Sub Now considering that you are looking to do this "164 times with slight variations each time" I would look for something "common" between all changes to condense the code instead of simply repeating the original code "164 times with slight variations each time". If the 2 examples you gave are indicative of a pattern i.e. A change in Column B means do something to Column A, offset by 1 row, then the code below might work. If there is no pattern then you may very well have to repeat the code sections "164 times with slight variations each time". One more note before I offer the new code: If a Worksheet_Change macro makes a change to a worksheet, the macro may fire again because the worksheet changed again. That is why you need to set EnableEvents to False at the start of the marco and reset it to True before exiting. Now, since none of us are perfect, while we are testing our code, it is very possible for EnableEvents to get set to False and then have the code fail. Since EnableEvents doesn't get reset to True, the code won't fire when you make a change to the worksheet. You'll pull your hair out trying to fix your code, when the real issue is that EnableEvents is set to False. For that reason it helps to keep this little macro available to reset EnableEvents to True if required: Sub ResetEvents() Application.EnableEvents = True End Sub Here's the code that will fire on any change to Column B: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False 'Fire on change to Column 2 only If Target.Column = 2 Then 'Capture Target row rwNum = Target.Row 'If Target = 3, delete validation list and set (A & Row + 1) = (A & Row) If Target = 3 Then With Range("A" & rwNum + 1).Validation .Delete End With Range("A" & rwNum + 1) = Range("A" & rwNum) 'Else clear (A & Row + 1) and add Data Validation list. Else Range("A" & rwNum + 1) = "" With Range("A" & rwNum + 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$D$1:$D$5" .IgnoreBlank = True .InCellDropdown = True End With Range("A" & rwNum + 1).Activate End If End If Application.EnableEvents = True End Sub

what if i need to apply it to 2 seperate columns that do the same thing once to column A from column B like above but also from Column F to effect Column G? Thanks Nevermind, I figured it out thank you so much

I am trying to make an if statement that accomplishes something like, If c1=b1 then d1 = pick choice from the data validation list(drop down list) that is from e1 to e5. else pick choice from the data validation list(drop down list) that is from f1 to f5.