Checking if Target is within different ranges
 

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

Checking if Target is within different ranges
 

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

"Matt" wrote in message
...
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.")
End If
End With
End If