Runtime Error 13 problem

[TimPin]

I am working on a simple spreadsheet that changes characters when you click on the cell, then counts the cells that are of a certain character. I have set the range to only look at certain cells (A3:A27, B3:B27) but anytime I click on row 1 (Row with several merged cells) or select multiple cells that are outside of the defined range I get a Runtime Error 13. Here is my code. Thank you in advance for the help!!

<code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

Set rng = Range("A3:A27,B3:B27")
If Target.Value = Chr(254) Then
Target.Value = Chr(168)
Cancel = True
ElseIf Target.Value = Chr(168) Then
Target.Value = Chr(254)
Cancel = True
End If
End Sub
</code>

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A3:B27")) Is Nothing Then Exit Sub
    If Target.Value = Chr(254) Then
        Target.Value = Chr(168)
        Cancel = True
    ElseIf Target.Value = Chr(168) Then
        Target.Value = Chr(254)
        Cancel = True
    End If
End Sub

 

[Norie]

Your code isn't restricted to the range 'A3:A27, B3:B27', it will look at any cell that has changed.

Try this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

    Set rng = Range("A3:A27,B3:B27")

    If Intersect(Target, rng) Is Nothing Then Exit Sub

    If Target.Value = Chr(254) Then
        Target.Value = Chr(168)
    ElseIf Target.Value = Chr(168) Then
        Target.Value = Chr(254)
    End If

End Sub

 

[TimPin]

Thank you so much that fixed my problem. Sorry for the delayed response, I was on vacation.

 

[TimPin]

I applied this code to another worksheet and I am now getting another runtime 13 error if the user selects more than one cell in the range. My code has been modified as such:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("E3:I30")) Is Nothing Then Exit Sub
    If Target.Value = "" Then
        Target.Value = Chr(80)
        Cancel = True
    ElseIf Target.Value = Chr(80) Then
        Target.Value = ""
        Cancel = True
    End If
End Sub

 

[DanteAmor]

The error occurs because in Target you have a range of cells, if you select several cells then in target you have a set of cells, so you can not collect a set of cells against a single value: Target.Value = ""

Now you can select more than one cell and only the cells in the "E3:I30" range will be the ones that change.

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set r = Range("[COLOR=#0000ff]E3:I30[/COLOR]")
    If Intersect(Target, r) Is Nothing Then Exit Sub
    If Target.Count > r.Count Then Exit Sub
    
    Application.ScreenUpdating = False
    For Each c In Target
        If Not Intersect(c, r) Is Nothing Then
            If c.Value = "" Then
                c.Value = Chr(80)
            ElseIf c.Value = Chr(80) Then
                c.Value = ""
            End If
        End If
    Next
End Sub

Nota:
Cancel = True
It is not necessary

 

[TimPin]

Thank you SO much, I greatly appreciate your help...I'm learning.

 

[DanteAmor]

Thank you SO much, I greatly appreciate your help...I'm learning.

Everyone! we are always learning.
I'm here to help you with pleasure. Thanks for the feedback.