Selecting range of cells whilst 'event selection procedure' runs in the background

Hi.
I'm having some trouble with target cells in event procedure, when i select any range of more than two cells (for cell formatting purposes) the debugger goes on but not sure why?
Here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 28 And Target.Row > 31 And Target.Row < 531 And Target.Value = "UnSelected" Then
        Target.Value = "Selected"
        With Range(Target.Offset(0, -21).Address & ":" & Target.Offset(0, 4).Address)
            .Interior.Color = RGB(204, 255, 204)
        End With
    ElseIf Target.Column = 28 And Target.Row > 31 And Target.Value = "Selected" Then
        Target.Value = "UnSelected"
        With Range(Target.Offset(0, -21).Address & ":" & Target.Offset(0, 4).Address)
            .Interior.ColorIndex = xlNone
        End With
    End If
End Sub

I added this and it works now

If Target.Row < 31 Then Exit Sub
If Target.Row > 532 Then Exit Sub
If Target.Column < 28 Then Exit Sub
If Target.Column > 28 Then Exit Sub

Hi,

Your problem was caused by Target.Row and Target.Column and Target.Value being illegal when more than one cell was selected.

If you limit selection to one cell only try the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.CountLarge > 1 Then
      MsgBox "Selection of more than one cell is not allowed."
      Exit Sub
    End If
   
    If Target.Column = 28 And Target.Row > 31 And Target.Row < 531 And Target.Value = "UnSelected" Then
        Target.Value = "Selected"
        With Range(Target.Offset(0, -21).Address & ":" & Target.Offset(0, 4).Address)
            .Interior.Color = RGB(204, 255, 204)
        End With
    ElseIf Target.Column = 28 And Target.Row > 31 And Target.Value = "Selected" Then
        Target.Value = "UnSelected"
        With Range(Target.Offset(0, -21).Address & ":" & Target.Offset(0, 4).Address)
            .Interior.ColorIndex = xlNone
        End With
    End If
End Sub

Please NOTE: When using Excel 2003 replace CountLarge with Count, because CountLarge is NOT available in Excel 2003.



If selecting more than one cell is allowed try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rCell As Range
    
    For Each rCell In Target
    
        If rCell.Column = 28 And rCell.Row > 31 And rCell.Row < 531 And rCell.Value = "UnSelected" Then
            rCell.Value = "Selected"
            With Range(rCell.Offset(0, -21).Address & ":" & rCell.Offset(0, 4).Address)
                .Interior.Color = RGB(204, 255, 204)
            End With
        ElseIf rCell.Column = 28 And rCell.Row > 31 And rCell.Value = "Selected" Then
            rCell.Value = "UnSelected"
            With Range(rCell.Offset(0, -21).Address & ":" & rCell.Offset(0, 4).Address)
                .Interior.ColorIndex = xlNone
            End With
        End If
    
    Next rCell
    
End Sub

Lewis

Your solution in post #2 still causes a runtime error for me, when selecting more than one cell.

Dear Lewis,
I actually did want to have the option of selecting more than one cell but i thought that would be impossible so i inserted in my code the exit sub if i selected anything outside the range mentioned in the code. It did not take care of the issue if i selected more than one cell within the range mentioned in the code, but once again, i thought there was no way out.
The second code you posted is originally what i was looking for and it works flawlessly! Thank you.

P.S. sometimes i think it would be impossible to get a solution to certain vba questions i have but then i am proven wrong.
You inspire me

Thank you.