Check to see if selected cell is between two others?

[ceyhorn]

How would I check to see if the selected cell is under a certain cell called "personnel" and above a cell called "contractual"? They are all in the same column "B".
thanks in advance,
Chris

 

[PHV]

Take a look at the Intersect method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Molby]

Not tested

If activecell.offset(0,-1).value = "personnel" and Activecell.offset(0,1).value = "contractual" then
msgbox("Yes it is")
Else
msgbox("No it isn't")
end if

 

[SkipVought]

Hi,

If you mean that the Range Names are personnel and contractual respectively...

ra = ActiveCell.Row
rp = Range("personnel").Row
rc = Range("contractual").Row
Select Case ra
  Case Is <= rp
     MsgBox "Above personnel"
  Case Is >= rc
     MsgBox "below contracutal"
  Case Else
     MsgBox "between"
End Select

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Loomah]

Hi
A couple more options to play with

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > Range("personnel").Row _
        And Target.Row < Range("contractual").Row _
        And Target.Column = 2 Then
            MsgBox "Without Intersect:" & vbCrLf & _
                "Seletion is below ""personnel"" and above ""contractual"""
    End If

'OR

    If Target.Row > Range("personnel").Row _
        And Target.Row < Range("contractual").Row _
        And Not Intersect(Target, Columns(2)) Is Nothing Then
            MsgBox "With Intersect:" & vbCrLf & _
                "Seletion is below ""personnel"" and above ""contractual"""
    End If
    
    If Target.Offset(-1, 0).Address = Range("personnel").Address Then
        MsgBox "Selection is directly below ""personnel"""
    End If
    
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[ceyhorn]

Thanks for the help,
The selected cell could be x amount of cells below or above the personnel or the contractual cell.
Personal
x
x
x
selected cell
x
x
Contractual
x
x
y
Travel

I want to make sure that "selected cell" is in the right budget section and not between "contractual" and "travel"

thanks again,
Chris