Worksheet Change by Value

[Aussie Grid]

I would like to be able to compare the target value to that of a range of cells, thus avoiding code for every target cell.
Currently: If Target.Offset(0, -1).Value = Worksheets("Admin").Range("S33")
If it could be changed to look for the target cell value eg. Worksheets("Admin").Range("S33:S40") it would save a lot of code as Ialready have about 240 lines of code in the event.
Would it be better as a case statement?

Any & all help appreciated

Aussie Grid

 

[Stephen_Tsui]

See if below what u want

for i = 1 to worksheets("Admin").Range("S33:S40").rows.count
     if Target.offset(0,-1).value = worksheets("Admin").Range("S33:S40").cells(i)
         Msgbox "Hi"
     End if
Next i

 

[lenze]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > i Then Exit Sub
Dim C As Range
Set C = Range("S33:S40").Find(Target.Offset(0, -1), LookIn:=xlValues)
If Not C Is Nothing Then
      MsgBox "Found in " & C.Address
Else: MsgBox "Not Found"
End If
End Sub

lenze

 

[Stephen_Tsui]

Or code like below:
Just need to chage The "S3:10,R6:R18" to the range u want.

Sub Try()

Dim RangArea As Range
Dim i, j As Integer

Set RangArea = Worksheets("Admin").Range("S3:S10,R6:R18")

For i = 1 To RangArea.Areas.Count
    For j = 1 To RangArea.Areas(i).Cells.Count
        If Target.Offset(0, -1).Value = RangArea.Areas(i).Cells(j).Value Then
            MsgBox "Hi"
        End If
    Next j
Next i

End Sub

 

[Aussie Grid]

Thank you both for your time and effort they all worked perfectly and will save a lot of typing.

Thank you

Aussie Grid