VBA If/Then with formula and locks/unlocks cell

[csimarketing]

I am new to writing macros and am struggling with this one.

I would like the following to happen automatically:

If Cell "C8" equals "NEW" then unlock Cell "F8" and allow someone to type in any value in Cell "F8".
If Cell "C8" does NOT equal "NEW" then lock Cell "F8" and run this formula: "=VLOOKUP(C8,Dropdowns!A3:B273,2,FALSE)"


I originally had a dependent dropdown list where C8 was our customer # and F8 was the customer name and autopopulates based on the value in C8.

However, now we need to be able to enter in a New Customer that is not in the predetermined dropdown, while also keeping the autopopulation function above if an existing customer is entered in C8.

Here is my current VBA, but it doesn't work...





Private Sub Workbook_Change(ByVal Target As Range)
If Range("C8") = "NEW" Then
Range("F8").Locked = False
ElseIf Range("C8") <> "NEW" Then
Range("F8").Locked = True
ElseIf Range("C8") <> "NEW" Then
Else: Range("F8").Formula = "=VLOOKUP(C8,Dropdowns!A3:B273,2,FALSE) "

End If
End Sub



Any help would be much appreciated! Thanks!

 

[csimarketing]

Password is "test"

 

[Fluff]

Ok, thanks for that.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C8" Then
      Me.Unprotect "test"
      If Target.Value = "NEW" Then
         Range("F8").Value = ""
         Range("F8:j8").Locked = False
      Else
         Range("F8").Formula = "=VLOOKUP(C8,Sheet2!A2:B13,2,FALSE)"
         Range("F8:J8").Locked = True
      End If
      Me.Protect "test"
   End If
End Sub

 

[csimarketing]

This works perfect! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback