Checking if Target is within different ranges

[Guest]

I would like to do an if/elseif statement or case statement to determine if the cell the user entered data in is in 7 different ranges. Depending on which range it is in I would like to check the value of the cell with the value of another cell(different cell for each range) and based on that check display a msgbox. This is what I have so far. I just need to know how to change this so that the cell that the user entered data into is compared to 7 different ranges. The ranges are C13:C15, E13:E15, G13:G15, I13:I15, K13:K15, M13:M15, O13:O15. Right now my code only checks to see if the target is in the C13:C15 range. I think a case statement would be better, but I'm not sure. In the case statement, for each case I will be comparing the cell with a different cell. Thanks. Matt

If cbExempt.Value = True Then
If Intersect(Target, [C13:C15]) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "AL" And Not IsEmpty(Range("D10")) Then
MsgBox ("Please enter ADDHR = number of hours worked and reason on the overtime explanation at the bottom.")
ElseIf .Value = "HDAY" And Not IsEmpty(Range("D10")) Then
MsgBox ("Please enter HOLWK = number of hours worked and reason on the overtime explanation at the bottom.")
End If
End With
End If

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range, rng2 as Range
Dim i as Long, bIntersect as Boolean
' enter your 7 cells to check against.
bIntersect = False
varr = vArray("D10","M12", . . . ,"Z21")
set rng = Range("C13")
for i = 1 to 7
set rng1 = rng.Offset(0, (i - 1) * 2).Resize(3, 1)
if not intersect(target, rng1) is nothing then
set rng2 = Range(varr(i))
' perform your checks
bIntersect = True
exit for
Next
' if you have processing below this, then you
' can exit if no intersect was made
' otherwise you don't need to check.
if bIntersect = False then exit sub

End if

--
Regards,
Tom Ogilvy

I would like to do an if/elseif statement or case statement to determine

if the cell the user entered data in is in 7 different ranges. Depending on
which range it is in I would like to check the value of the cell with the
value of another cell(different cell for each range) and based on that check
display a msgbox. This is what I have so far. I just need to know how to
change this so that the cell that the user entered data into is compared to
7 different ranges. The ranges are C13:C15, E13:E15, G13:G15, I13:I15,
K13:K15, M13:M15, O13:O15. Right now my code only checks to see if the
target is in the C13:C15 range. I think a case statement would be better,
but I'm not sure. In the case statement, for each case I will be comparing
the cell with a different cell. Thanks. Matt

If cbExempt.Value = True Then
If Intersect(Target, [C13:C15]) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "AL" And Not IsEmpty(Range("D10")) Then
MsgBox ("Please enter ADDHR = number of hours worked and

reason on the overtime explanation at the bottom.")

ElseIf .Value = "HDAY" And Not IsEmpty(Range("D10")) Then
MsgBox ("Please enter HOLWK = number of hours worked and r

eason on the overtime explanation at the bottom.")