A better way for If and Or condition in VBA

[danjuma]

Hello. I have the VBA code below. What it does is check if the data entered in a cell in column R is valid and then lock the cell and cells on the row from columns A to Q. What I am after is a better/more efficient for the condition "If Target = "123456" Or Target = "3245671" Or Target = "4227716" Then" as there are up to 20 different set of numbers I want to validate else I will have a very long If Or statement. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
 ActiveSheet.Unprotect Password:="mypassword"
 If Target = "123456" Or Target = "3245671" Or Target = "4227716" Then
 Range("A" & Target.Row & ":R" & Target.Row).Locked = True
 End If
 ActiveSheet.Protect Password:="1855nom"
End Sub

 

[danjuma]

Cheers, hope it works for you.

Hello Peter_SSs, the code works fine, thank you. Just one little issue, for some reason when typing data into the cells in a worksheet (that the code is on), the text does not show (or to be more specific, it shows as white font, so you can't see what you are actually typing on a white sheet) until you finish typing and press enter/click out of the cell, then the text shows (black text). Not sure why this is? Thanks

 

[Peter_SSs]

That is unrelated to the code I suggested. I think you must have some special formatting in that column, or Conditional Formatting, or other vba code that if affecting it.

 

[danjuma]

That is unrelated to the code I suggested. I think you must have some special formatting in that column, or Conditional Formatting, or other vba code that if affecting it.

Okay, no worries. Many thanks.