multiple value choices for If range().value = "xxx"

  • Thread starter Thread starter cm
  • Start date Start date
C

cm

The macro below runs on worksheet change and works properly.

However, I have 5 values to examine in column H. The values are: Split,
Payday 1, Payday 2, Payday 3, and Payday 4. Any of these choices should
result in unlocking and clearing the cells in that row (first part of if
statement). Otherwise, the range 'pasterage' is copied to that row and it is
locked again. I can't find the proper syntax (if there is any) to say
something like is possible in SQL (if ...value in ("Split","Payday 1",...))
Please advise on the best way to go about this.

n = Target.Row
If Me.Range("H" & n).Value = "Split" Then
Me.Range("I" & n & ":W" & n).Locked = False
Me.Range("I" & n & ":W" & n).ClearContents
Else
Me.Range("I" & n & ":W" & n).Locked = True
Range("pasterange").Copy
Range("I" & n).PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End If
 
There is no "value in ()" syntax.

You can use this:
Select Case Range("H" & n).Value
Case "Split", "Payday 1", "Payday2"
[do something]
Case "Payday 3", "Payday4"
[do something else]
End Select

Or this:
If Instr("Split Payday 1 Payday2", Range("H" & n).Value) Then
 
Back
Top