Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Condition for range of cells

    Is there a way to write a condition so that if a key on certain range of cells is pressed then a msgbox is displayed else some other message box is displayed.
    Eg. Range ("A10:B25") .... msgbox "Range 1"
    Range ("A40:B60") .... msgbox "Range 2"

    Thanks.

    ~BS

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached Excel file will demonstrate how to display the applicable message "Range 1" or "Range 2" when any cells within the two ranges from your example are selected. Maybe someone in the forum can improve on this.

    The VBA code was entered after right-clicking the tab label "Sheet1", and selecting "View Code", then use the drop-down arrow that displays "General" to choose "Worksheet". This creates the first and last line of the following code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Row >= 10 And ActiveCell.Row <= 25 Then
    If ActiveCell.Column <= 2 Then MsgBox "Range 1"
    End If

    If ActiveCell.Row >= 40 And ActiveCell.Row <= 60 Then
    If ActiveCell.Column <= 2 Then MsgBox "Range 2"
    End If

    End Sub

    Jerry
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    Hi Jerry,
    I guess your code with a little bit of modifications should work for me.

    Thanks.

    ~BS

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Just thought id post this as a variation on a theme

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim isect As Range
        
        Set isect = Application.Intersect(Target, Range("A10:B25"))
        If Not isect Is Nothing Then MsgBox "A"
        
        Set isect = Application.Intersect(Target, Range("A40:B60"))
        If Not isect Is Nothing Then MsgBox "B"
    End Sub
    Dave
    Last edited by DavidCoutts; 02-01-05 at 05:42.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •