Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Error Checking doesn't work on column ranges? | New Users to Excel | |||
Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
How find if target is object in Worksheet_Change (ByVal Target As.. ?) | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming |